May 25, 2010 at 11:53 pm
Hi
May you please help/guide with a query that would break a 24 hours day into 30 minutes intervals.
the results should look like :-
StartTime EndTime
2010-05-26 09:00:00 2010-05-26 09:30:00
2010-05-26 09:30:00 2010-05-26 10:00:00
2010-05-26 10:00:00 2010-05-26 10:30:00
2010-05-26 10:30:00 2010-05-26 11:00:00
Please help
May 26, 2010 at 1:01 am
Hey you can use the date add function to increment minutes...
the syntax is dateadd(minute,"number by which you want to increment","which you want to increment")
use this date add and produce the result which you want to produce
If you want to pass start time and end time as parameters please create a stored proc like in example below
create procedure printtimeintervals @starttime datetime,@endtime datetime
as
while (@Starttime<=@endtime)
begin
Select @starttime as Starttime, DATEADD(minute,30,@starttime) as Endtime
set @starttime=DATEADD(minute,30,@starttime)
end
To pass the parameters and get the results use the below query
exec
printtimeintervals
@starttime='5/26/2010',
@endtime='5/27/2010';
May 26, 2010 at 1:54 am
Thanks member will let you know how it goes
May 26, 2010 at 3:04 am
You can also use a Tally Table for the same. Once you create a Tally table you can use the script given below to get the desired results.
DECLARE @sdtStartTime SMALLDATETIME
DECLARE @sdtEndTime SMALLDATETIME
DECLARE @iSlots INT
SET @sdtStartTime = '26-May-2010'
SET @sdtEndTime = '27-May-2010'
SET @iSlots = DATEDIFF( HOUR, @sdtStartTime, @sdtEndTime ) * 2
SELECT DATEADD( MINUTE, 30 * N , @sdtStartTime ) Date
FROM dbo.Tally
WHERE N <= @iSlots
The link below gives you a script for creation of sample Tally Table
http://qa.sqlservercentral.com/scripts/Advanced+SQL/62486/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 26, 2010 at 6:09 am
Hi Guys
thanks for the feedback.
The data in my table is as follows
ScenarioID StartTime StaffingRequirement ActiveStaff
1 2009/12/27 00:00 4 3
1 2009/12/27 00:15 3 3
1 2009/12/27 00:30 6 4
1 2009/12/27 00:45 8 5
1 2009/12/27 01:00 5 5
1 2009/12/27 01:15 3 2
1 2009/12/27 01:30 3 0
1 2009/12/27 01:45 2 1
1 2009/12/27 02:00 6 6
1 2009/12/27 02:15 7 6
currently in 15 minute intevals, however the client would like to see it in 30 minute intervals. So what I really need is, using the examples you provided I would like to sum the staffingrequirement and activestaff by 30 minute intervals
sorry for not being clear in the first place
May 26, 2010 at 7:01 am
Its still not very clear. Can you provide the expected result. That would certainly help people help you. And if you provide it in a ready to use format as mentioned in the link in my signature, you will definitely get faster answers.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 26, 2010 at 11:27 am
select ScenarioID,
dateadd(minute,
(datepart(minute, StartTime) / 30) * 30,
dateadd(hour,
datepart(hour, starttime),
convert(datetime, convert(varchar, StartTime, 1)))),
sum(StaffingRequirement), sum(ActiveStaff)
from tbl
group by ScenarioID,
dateadd(minute,
(datepart(minute, StartTime) / 30) * 30,
dateadd(hour,
datepart(hour, starttime),
convert(datetime, convert(varchar, StartTime, 1))))
May 27, 2010 at 12:44 am
Thank Susan, that works like magic
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply