timeslot wise data - 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

timeslot wise data

Author

Topic

harminder59
Starting Member

2 Posts

Posted - 2011-05-25 : 06:31:41

I have huge data in table date wise. and i want to show that data in a time slot wise window for whole day, like i divide the whole day 24 hours into a 96 slots and taken 15 min interval for each slot.
now my first slot is 00:00 to 00:15 and secand is 00:15 to 00:30 and soon.now i want to show data like
time slot count
00:00 to 00:15 100
00:15 to 00:30 200
00:30 to 00:45 500
00:45 to 01:00 300
01:00 to 01:15 560
01:15 to 01:30 500

......
for whole day.

i have written a very big procedure for that in which i am loping the data again and again and which is very slow also.....
what i have done .. i have created two time interval variables and increamenting these variables out side my loop and fetching data like where datecolumn is between date firsttimeslot and secondtimeslot..

i want your suggestions for how we can do this in a efficient manner.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-25 : 07:14:54

Hi,
you can get easily 96 slots and taken 15 min interval for each slot
check it once


with cte as  
(  
select   convert(datetime,CONVERT(VARCHAR(15), getdate(), 112)) as DATE  
 
union all  
  
select   c.DATE+'00:15:00' as QuarterTime  
from cte c   
where c.date<dateadd(minute,1422,convert(datetime,CONVERT(VARCHAR(15), getdate(), 112)))
)
select * from cte

--result(96 rows for a day)
2011-05-25 00:00:00.000
2011-05-25 00:15:00.000
. . .
. . .
. . .
. . .
. . .
2011-05-25 23:30:00.000
2011-05-25 23:45:00.000

--Ranjit

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 07:15:00

You can round down the time to 15 minute intervals by doing something like this, where the last column will show the rounded time:


SELECT 
   *,
   DATEADD(mi,DATEDIFF(mi,0,YourDateColumn )/15*15,0) as RoundedTime
FROM
   YourTable

If you want to pick data only for any particular 15 minute slot, you could then specify that in the where clause, for example:


SELECT 
   *,
   DATEADD(mi,DATEDIFF(mi,0,YourDateColumn )/15*15,0) as RoundedTime
FROM
   YourTable
WHERE
   DATEADD(mi,DATEDIFF(mi,0,YourDateColumn )/15*15,0) as RoundedTime = '2011-05-25 07:15:00.000'

That would pick all the rows where time is between 7:15 and 7:30.

If you want to add a slot number to your query you can do the following:


SELECT 
   *,
   DATEADD(mi,DATEDIFF(mi,0,YourDateColumn )/15*15,0) as RoundedTime,
   DATEDIFF(mi,DATEADD(dd,DATEDIFF(dd,0,YourDateColumn ),0),YourDateColumn )/15 as SlotNumber
FROM
   YourTable

Here I have added a column to show the 15 minute slot number which would go from 0 to 95. Then, you could use that in your where clause if you prefer etc. etc.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 07:15:46