Query help - 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

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

gvmk27
Starting Member

44 Posts

Posted - 2012-12-14 : 16:48:43

Hi I need help in building the query

I have DateSetting table, structure of the table is

StartTime time(0)
EndTime time(0)
TimeSlotDuration smallint

records will be
StartTime EndTime TimeSlotDuration
10:00:00 13:00:00 30

basing on the TimeSlotDuration , I need to retrieve all the timeslots for the above duration, output should be
10:00:00 10:30:00
10:30:00 11:00:00
11:00:00 11:30:00
11:30:00 12:00:00
12:00:00 12:30:00
12:30:00 13:00:00

if the timeslotduration is 60, then output should be
10:00:00 11:00:00
11:00:00 12:00:00
12:00:00 13:00:00

Thanks in advance.
MOhan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-14 : 17:52:51

[code]SELECT
t.*,
DATEADD(mi,number*TimeSlotDuration,StartTime) AS TimeSlot
FROM
DataSettingTable t
INNER JOIN MASTER..spt_values s
ON s.number BETWEEN 0 AND DATEDIFF(mi,StartTime,EndTime)/TimeSlotDuration
WHERE
s.type = 'P'
ORDER BY
t.StartTime,
t.endTime,
t.TimeSlotDuration,
TimeSlot;[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-15 : 12:27:04

[code]
;With TimeSlots (Start,End,TimeSlot)
AS
(
SELECT StartTime, EndTime,TimeSlotDuration,StartTime AS SlotStart,DATEADD(minute,TimeSlotDuration,StartTime) AS SlotEnd
FROM Table
UNION ALL
SELECT StartTime, EndTime,TimeSlotDuration,SlotEnd, DATEADD(minute,TimeSlotDuration,SlotEnd)
FROM TimeSlots
WHERE DATEADD(minute,TimeSlotDuration,SlotEnd) <= EndTime
)

SELECT SlotStart,SlotEnd
FROM TimeSlots
OPTION (MAXRECURSION 0)
[/code]

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

gvmk27
Starting Member

44 Posts

Posted - 2012-12-17 : 11:37:24

Hi sunitabeck

Thanks for your reply, but I din't get what is MASTER..spt_values in the below SQL ?

quote:

Originally posted by sunitabeck

SELECT
	t.*,
	DATEADD(mi,number*TimeSlotDuration,StartTime) AS TimeSlot
FROM
	DataSettingTable t
	INNER JOIN MASTER..spt_values s
		ON s.number BETWEEN 0 AND DATEDIFF(mi,StartTime,EndTime)/TimeSlotDuration
WHERE
	s.type = 'P'
ORDER BY
	t.StartTime,
	t.endTime,
	t.TimeSlotDuration,
	TimeSlot;

gvmk27
Starting Member

44 Posts

Posted - 2012-12-17 : 11:40:02

Hi visakh16

Thanks for your reply, but it didn't worked.

I just have one table i.e DateSetting table. and there is no slotend column in the table.

Can you please help ?

quote:
Originally posted by visakh16

;With TimeSlots (Start,End,TimeSlot)
AS
(
SELECT StartTime, EndTime,TimeSlotDuration,StartTime AS SlotStart,DATEADD(minute,TimeSlotDuration,StartTime) AS SlotEnd
FROM Table
UNION ALL
SELECT StartTime, EndTime,TimeSlotDuration,SlotEnd, DATEADD(minute,TimeSlotDuration,SlotEnd)
FROM TimeSlots
WHERE DATEADD(minute,TimeSlotDuration,SlotEnd) <= EndTime
)

SELECT SlotStart,SlotEnd
FROM TimeSlots
OPTION (MAXRECURSION 0)

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-17 : 12:04:28

Are you seeing any errors when you use master..spt_values?

spt_values is a system table in the master database. It is undocumented, but it is routinely used when you want to use a series of sequential numbers. If you have a numbers table (a table that has an int column with sequential values starting at 1 or 0 and going up to some large number), you can use that instead of spt_values table.

gvmk27
Starting Member

44 Posts

Posted - 2012-12-17 : 15:50:01

Hi

It was working as expected, when I saw your reply I didnt have SQL tool handy to run. I just ran now and it was good.

Thanks a lot for your help. Hope I can use master..spt_values in production also as you mentioned it is undocumented.

MOhan

quote:
Originally posted by sunitabeck

Are you seeing any errors when you use master..spt_values?

spt_values is a system table in the master database. It is undocumented, but it is routinely used when you want to use a series of sequential numbers. If you have a numbers table (a table that has an int column with sequential values starting at 1 or 0 and going up to some large number), you can use that instead of spt_values table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 00:54:16

quote:
Originally posted by gvmk27

Hi visakh16

Thanks for your reply, but it didn't worked.

I just have one table i.e DateSetting table. and there is no slotend column in the table.

Can you please help ?

quote:
Originally posted by visakh16

;With TimeSlots (Start,End,TimeSlot)
AS
(
SELECT StartTime, EndTime,TimeSlotDuration,StartTime AS SlotStart,DATEADD(minute,TimeSlotDuration,StartTime) AS SlotEnd
FROM Table
UNION ALL
SELECT StartTime, EndTime,TimeSlotDuration,SlotEnd, DATEADD(minute,TimeSlotDuration,SlotEnd)
FROM TimeSlots
WHERE DATEADD(minute,TimeSlotDuration,SlotEnd) <= EndTime
)

SELECT SlotStart,SlotEnd
FROM TimeSlots
OPTION (MAXRECURSION 0)

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

SlotEnd is column i generated in CTE and you can always generate it on the fly.
The reason why I prefer this solution is it doesnt use any system table like spt_values which is undocumented and it makes use of recursive CTE which your yourself creat on the fly

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources