Group consecutive Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group consecutive Rows

Author

Topic

Firemaple
Starting Member

14 Posts

Posted - 2010-05-07 : 12:16:54

Ok, I have looked around and found some similar situations, but however I have not been able to apply the ideas to mine. So I thought I would ask for a bit of help from the experts.

Currently I have a table that looks like this:


ID      Slot                    Allowed Taken
----    --------------          ------- -----
137	2010-05-07 05:00:00.000	23	21
138	2010-05-07 05:15:00.000	15	5
139	2010-05-07 05:30:00.000	13	12
140	2010-05-07 05:45:00.000	20	8
141	2010-05-07 06:00:00.000	20	20
142	2010-05-07 06:15:00.000	10	7
143	2010-05-07 06:30:00.000	10	10
144	2010-05-07 06:45:00.000	6	6
145	2010-05-07 07:00:00.000	11	11
etc.

The slots increment every 15 minutes.

I am searching for consecutive available slots. (taken < allowed)
i.e. I want to set an appointment that is going to last 1 hour, so i need to find 4 consecutive available slots.

I can do this using the application code, but it would be a lot prettier if I could get the potential slots from the server.

Any suggestions on this would be great!

Thank in advance!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 13:52:07

The below code is just to start with:

Declare @DatTable table
(Id int,
Slot Datetime,
Allowed int,
Taken int)

Insert into @DatTable
Select 137,'2010-05-07 05:00:00.000',23,21 union
Select 138,'2010-05-07 05:15:00.000',15,5 union
Select 139,'2010-05-07 05:30:00.000',13,12 union
Select 140,'2010-05-07 05:45:00.000',20,8 union
Select 141,'2010-05-07 06:00:00.000',20,20 union
select 142 ,'2010-05-07 06:15:00.000', 10, 7 union
select 143 ,'2010-05-07 06:30:00.000', 10, 10 union
select 144 ,'2010-05-07 06:45:00.000', 6, 6 union
select 145 ,'2010-05-07 07:00:00.000', 11, 11 union

Select dt.id, (Select min(st.Allowed - st.Taken) from @DatTable ST Where St.id between
dt.id and dt.id + 3) from @DatTable dt

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources