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.
| 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. |
 |
|
|
|
|
|
|
|
Group consecutive Rows