Select Table with Day and Time Slot Interval

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 2008 Forums

Transact-SQL (2008)

Select Table with Day and Time Slot Interval

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-08-28 : 07:13:24

Table 1:
[Channel] [Product]
100 11
100 12
100 13
101 14
101 15
102 16
102 17

I need this above table with day and time slot interval of 30 mins.
Results will be like below table

[Channel] [Day] [StartTime] [EndTime]
100 1 00:00:00 00:30:00
100 1 00:30:00 01:00:00
......
......
......
......
100 1 23:00:00 23:30:00
100 1 23:30:00 24:00:00

The above content must repeat for another 6 days. I mean upto 7 days. For all channels as mentioned above for Channels 100,101,102. Product can be avoided here.

Please help me in this built a query with Channel with Day and Time Interval ASAP. Its an urgent one.

Regards,
Kalaiselvan R
Love Yourself First....

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-28 : 18:16:39

create a temp table or table variable with all the time slots for day 1 - 7
cross apply your table to it

If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 00:52:08

something like


;With Time_Slots(Start,[End])
AS
(
SELECT cast('00:00' as datetime),cast('00:30' as datetime) 
union all
SELECT DATEADD(mi,30,Start),DATEADD(mi,30,[ENd])
FROM Time_Slots
WHERE DATEADD(mi,30,[ENd])<1
),
[Days]([Day])
AS
(SELECT 1 AS [Day]
union all
SELECT [Day]+1
FROM [Days]
WHERE [Day]+1<=7
)


select Channel ,[Day],CAST(Start as Time) AS Start,CAST([End] as Time)  AS [End]
from (SELECT DISTINCT Channel FROM YourTable)t
CROSS JOIN [Days] d
CROSS JOIN Time_Slots t
ORDER BY Channel ,[Day],Start,[End]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources