Getting TimeSlots. - 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 2005 Forums

Transact-SQL (2005)

Getting TimeSlots.

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-11-29 : 07:08:43

Hi guys,

I have a table with following structure.I want to get the timeslot in a 1 hour period for each Id for each distinct day for a give time period.
Example

Create table @tbl(siteid int,dayid int,startdate datetime,enddate datetime)
insert into @tbl
select 31, 1, 2008-11-20 08:00:00.000, 2008-11-20 16:30:00.000 union all
select 31, 2, 2008-11-20 08:00:00.000, 2008-11-20 16:00:00.000 union all
select 31, 3, 2008-11-20 08:00:00.000, 2008-11-20 16:00:00.000 union all

same way till dayid 7 for tat particular siteid as there are seven days in a week

select 34, 1, 2008-11-29 08:15:00.000, 2008-11-29 16:15:00.000 union all
select 34, 2, 2008-11-29 08:00:00.000, 2008-11-29 16:30:00.000 union all
select 34, 3, 2008-11-29 08:45:00.000, 2008-11-29 16:45:00.000 union all

The op should be

31 ,1,8 -9
31 ,1,9 -10
31 ,1,10-11
31 ,1,11-12
31 ,1,12-13
31 ,1,13-14
|
|
|
31 ,1,15-16:30
Since the end time for dayid 1 for siteid 31 is 16:30 & not 16:00 the last slot for siteid 31 & dayid 1 is should come 31 ,1,15-16:30 & not 31 ,1,15-16:00 as 30 extra minutes are there.
Same way for siteid 34.

Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 08:41:25

[code];With CTE(siteid,dayid,slotstart,slotend)
AS
(
SELECT siteid,dayid,DATEPART(hh,startdate), DATEPART(hh,DATEADD(hh,1,startdate))
FROM YourTable
UNION ALL
SELECT c.siteid,c.dayid,DATEPART(hh,DATEADD(hh,1,c.slotstart)),
CASE WHEN c.slotend<t.enddate THEN DATEPART(hh,DATEADD(hh,1,c.slotend))
WHEN c.slotend=t.enddate THEN c.slotend
ELSE NULL
END
FROM CTE c
INNER JOIN yourtable t
ON t.siteid=c.siteid
AND t.dayid=c.dayid
WHERE c.slotend<=t.enddate
)

SELECT c.siteid,c.dayid,CAST(c.slotstart AS varchar(3))+ '-'+CAST(ISNULL(c.slotend,t.enddate) AS varchar(3))
FROM CTE c
INNER JOIN YourTable t
ON t.siteid=c.siteid
AND t.dayid=c.dayid[/code]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-12-01 : 08:35:46

Sorry Visakh for the late reply.I am not getting the expected result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 08:40:05

quote:
Originally posted by ayamas

Sorry Visakh for the late reply.I am not getting the expected result.

Give some more details please.
are you getting some error?
or is it that you're not getting expected results?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-12-01 : 08:57:05

Here is some part of the live data & when I execute it it gives error
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
The error is in the select query on the CTE.Also if I change it to just Select * from CTE the error is --

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

declare @tbl as table(DischargeSiteId int,dayid int,fromtime datetime,tilltime datetime)
insert into @tbl
select 31 , 1 , '2008-11-20 09:00:00.000',' 2008-11-20 16:30:00.000' union all
select 31 , 2 , '2008-11-20 09:00:00.000',' 2008-11-20 16:30:00.000' union all
select 31 , 3 , '2008-11-20 09:00:00.000',' 2008-11-20 16:30:00.000' union all
select 31 , 4 , '2008-11-20 09:00:00.000',' 2008-11-20 16:30:00.000' union all
select 31 , 5 , '2008-11-20 08:00:00.000',' 2008-11-20 16:30:00.000' union all
select 31 , 6 , '2008-11-20 00:00:00.000',' 2008-11-20 00:00:00.000' union all
select 31 , 7 , '2008-11-20 00:00:00.000',' 2008-11-20 00:00:00.000' union all
select 34 , 1 , '2008-11-29 08:15:00.000',' 2008-11-29 16:15:00.000' union all
select 34 , 2 , '2008-11-29 08:00:00.000',' 2008-11-29 16:30:00.000' union all
select 34 , 3 , '2008-11-29 08:45:00.000',' 2008-11-29 16:45:00.000' union all
select 34 , 4 , '2008-11-29 08:00:00.000',' 2008-11-29 16:00:00.000' union all
select 34 , 5 , '2008-11-29 08:00:00.000',' 2008-11-29 16:00:00.000' union all
select 34 , 6 , '2008-11-29 00:00:00.000',' 2008-11-29 00:00:00.000' union all
select 34 , 7 , '2008-11-29 00:00:00.000',' 2008-11-29 00:00:00.000'

;With CTE(DischargeSiteId,dayid,slotstart,slotend)
AS
(
SELECT DischargeSiteId,dayid,DATEPART(hh,FromTime), DATEPART(hh,DATEADD(hh,1,FromTime))
FROM siteopeninghours
UNION ALL
SELECT c.DischargeSiteId,c.dayid,DATEPART(hh,DATEADD(hh,1,c.slotstart)),
CASE WHEN c.slotend<t.TillTime THEN DATEPART(hh,DATEADD(hh,1,c.slotend))
WHEN c.slotend=t.TillTime THEN c.slotend
ELSE NULL
END
FROM CTE c
INNER JOIN siteopeninghours t
ON t.DischargeSiteId=c.DischargeSiteId
AND t.dayid=c.dayid
WHERE c.slotend<=t.TillTime
)

SELECT c.DischargeSiteId,c.dayid,CAST(c.slotstart AS varchar(3))+ '-'+CAST(ISNULL(c.slotend,t.TillTime) AS varchar(3))
FROM CTE c
INNER JOIN siteopeninghours t
ON t.DischargeSiteId=c.DischargeSiteId
AND t.dayid=c.dayid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:15:47

modify like this and try



;With CTE(DischargeSiteId,dayid,slotstart,slotend)
AS
(
SELECT DischargeSiteId,dayid,DATEPART(hh,FromTime), DATEPART(hh,DATEADD(hh,1,FromTime))
FROM siteopeninghours
UNION ALL
SELECT c.DischargeSiteId,c.dayid,DATEPART(hh,DATEADD(hh,1,c.slotstart)),
CASE WHEN c.slotend<DATEPART(hh,t.TillTime) THEN DATEPART(hh,DATEADD(hh,1,c.slotend))
WHEN c.slotend=DATEPART(hh,t.TillTime) THEN c.slotend
ELSE NULL
END
FROM CTE c
INNER JOIN siteopeninghours t
ON t.DischargeSiteId=c.DischargeSiteId
AND t.dayid=c.dayid
WHERE c.slotend<=DATEPART(hh,t.TillTime)
)


SELECT c.DischargeSiteId,c.dayid,CAST(c.slotstart AS varchar(3))+ '-'+CAST(ISNULL(c.slotend,DATEPART(hh,t.TillTime)) AS varchar(3))
FROM CTE c
INNER JOIN siteopeninghours t
ON t.DischargeSiteId=c.DischargeSiteId
AND t.dayid=c.dayid 

OPTION (MAXRECURSION 0)

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-12-01 : 09:37:43

Nope.It takes forever just for 14 records to execute.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:42:45

[code]
;With CTE(DischargeSiteId,dayid,slotstart,slotend)
AS
(
SELECT DischargeSiteId,dayid,DATEPART(hh,FromTime), DATEPART(hh,DATEADD(hh,1,FromTime))
FROM siteopeninghours
UNION ALL
SELECT c.DischargeSiteId,c.dayid,DATEPART(hh,DATEADD(hh,1,c.slotstart)),
DATEPART(hh,DATEADD(hh,1,c.slotend))
FROM CTE c
INNER JOIN siteopeninghours t
ON t.DischargeSiteId=c.DischargeSiteId
AND t.dayid=c.dayid
WHERE DATEPART(hh,DATEADD(hh,1,c.slotend))<DATEPART(hh,DATEADD(hh,1,t.TillTime))
)

SELECT * FROM CTE

OPTION (MAXRECURSION 0)
[/code]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-12-02 : 01:20:45

No still not working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 01:53:46

[code]declare @tbl table(siteid int,dayid int,startdate datetime,enddate datetime)
insert into @tbl
select 31, 1, '2008-11-20 08:00:00.000', '2008-11-20 16:30:00.000' union all
select 31, 2, '2008-11-20 08:00:00.000', '2008-11-20 16:00:00.000' union all
select 31, 3, '2008-11-20 08:00:00.000', '2008-11-20 16:00:00.000' union all
select 31, 4, '2008-11-20 08:00:00.000', '2008-11-20 15:30:00.000' union all
select 31, 5, '2008-11-20 09:45:00.000', '2008-11-20 14:50:00.000'

;With CTE(siteid,dayid,slotstart,slotend,enddate)
AS
(
SELECT siteid,dayid,startdate, DATEADD(hh,1,startdate),enddate
FROM @tbl
UNION ALL
SELECT c.siteid,c.dayid,slotend,
CASE WHEN DATEADD(hh,1,slotend)<=enddate THEN DATEADD(hh,1,slotend) ELSE enddate END,enddate
FROM CTE c
WHERE slotend<enddate
)

SELECT siteid,dayid,CONVERT(varchar(8),slotstart,8) AS slotstart,CONVERT(varchar(8),slotend,8) AS slotend
FROM CTE
ORDER BY siteid,dayid,slotstart

OPTION (MAXRECURSION 0)

Output
---------------------------------------------------
siteid dayid slotstart slotend
31 1 08:00:00 09:00:00
31 1 09:00:00 10:00:00
31 1 10:00:00 11:00:00
31 1 11:00:00 12:00:00
31 1 12:00:00 13:00:00
31 1 13:00:00 14:00:00
31 1 14:00:00 15:00:00
31 1 15:00:00 16:00:00
31 1 16:00:00 16:30:00
31 2 08:00:00 09:00:00
31 2 09:00:00 10:00:00
31 2 10:00:00 11:00:00
31 2 11:00:00 12:00:00
31 2 12:00:00 13:00:00
31 2 13:00:00 14:00:00
31 2 14:00:00 15:00:00
31 2 15:00:00 16:00:00
31 3 08:00:00 09:00:00
31 3 09:00:00 10:00:00
31 3 10:00:00 11:00:00
31 3 11:00:00 12:00:00
31 3 12:00:00 13:00:00
31 3 13:00:00 14:00:00
31 3 14:00:00 15:00:00
31 3 15:00:00 16:00:00
31 4 08:00:00 09:00:00
31 4 09:00:00 10:00:00
31 4 10:00:00 11:00:00
31 4 11:00:00 12:00:00
31 4 12:00:00 13:00:00
31 4 13:00:00 14:00:00
31 4 14:00:00 15:00:00
31 4 15:00:00 15:30:00
31 5 09:45:00 10:45:00
31 5 10:45:00 11:45:00
31 5 11:45:00 12:45:00
31 5 12:45:00 13:45:00
31 5 13:45:00 14:45:00
31 5 14:45:00 14:50:00[/code]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-12-02 : 02:09:29

Thanks Visakh atlast it works.But I wanted the last timeslot for 31 to be
31 ,1,15-16:30
Since the end time for dayid 1 for siteid 31 is 16:30 & not 16:00 the last slot for siteid 31 & dayid 1 should come 31 ,1,15-16:30 & not 31 ,1,15-16:00 as there are 30 extra minutes.

But anyways I found a way to acheive it.Thanks for all the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 02:32:04

quote:
Originally posted by ayamas

Thanks Visakh atlast it works.But I wanted the last timeslot for 31 to be
31 ,1,15-16:30
Since the end time for dayid 1 for siteid 31 is 16:30 & not 16:00 the last slot for siteid 31 & dayid 1 should come 31 ,1,15-16:30 & not 31 ,1,15-16:00 as there are 30 extra minutes.

But anyways I found a way to acheive it.Thanks for all the help.

ok. i thought that should be kep as seperate slot. thats why i handled it as another slot (if you see my output you have 1 more slot for 31 day 1 which is 16-16.30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources