September 30, 2013 at 4:29 pm
Hi. I'm working on creating a histogram using time, as the set of 6 buckets that data can fall into. ie. something can happen between Midnight and 4am, 4am and 8am, 8am and 12pm, and so on.
The column I have to break into these buckets is a datetime column.
First, I checked on how to extract the time from the datetime
convert(char(5), GETDATE(), 108)
Returns this format. 15:22<--Good
Next I am trying to create the case statement. See below. SQL doesn't like the Between 12:00 and 04:00.
select convert(char(5), GETDATE(), 108),
case convert(char(5), GETDATE(), 108) between 12:01 and 04:00 then MidnightToFour
else restOfTime
end as timeframe
Error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'between'.
How do I frame the condition so that Times falling between midnight and 4am are assigned "MidnighttoFour" value in the TimeFrame column?
--Quote me
September 30, 2013 at 4:37 pm
I would grab the minutes since midnight instead of the "time as a string" myself.
DATEDIFF(minute,CAST(my_datetime_column as DATE),my_datetime_column)
Then it is simple integer division by 240 to get each 4 hour bucket
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);September 30, 2013 at 4:56 pm
sounds good.
can you see what's wrong with the CASE statement?
, CASE
WHEN DATEDIFF(minute,CAST([ActualStartTime]+ GETDATE() - GETUTCDATE() as DATE),[ActualStartTime]+ GETDATE() - GETUTCDATE()) between 1 and 239 then MidnightTo4am
WHEN DATEDIFF(minute,CAST([ActualStartTime]+ GETDATE() - GETUTCDATE() as DATE),[ActualStartTime]+ GETDATE() - GETUTCDATE()) between 240 and 479 then 4amTo8am
else restoftime
end TimeFrame
getting error
Incorrect syntax near 'amTo8am'.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near 'TimeFrame'.
--Quote me
September 30, 2013 at 5:02 pm
I needed quotes around new values
, CASE
WHEN DATEDIFF(minute,CAST([ActualStartTime]+ GETDATE() - GETUTCDATE() as DATE),[ActualStartTime]+ GETDATE() - GETUTCDATE()) between 1 and 239 then 'MidnightTo4am'
WHEN DATEDIFF(minute,CAST([ActualStartTime]+ GETDATE() - GETUTCDATE() as DATE),[ActualStartTime]+ GETDATE() - GETUTCDATE()) between 240 and 479 then '4amTo8am'
WHEN DATEDIFF(minute,CAST([ActualStartTime]+ GETDATE() - GETUTCDATE() as DATE),[ActualStartTime]+ GETDATE() - GETUTCDATE()) between 480 and 719 then '8amToNOON'
WHEN DATEDIFF(minute,CAST([ActualStartTime]+ GETDATE() - GETUTCDATE() as DATE),[ActualStartTime]+ GETDATE() - GETUTCDATE()) between 720 and 999 then 'NOON_To4pm'
WHEN DATEDIFF(minute,CAST([ActualStartTime]+ GETDATE() - GETUTCDATE() as DATE),[ActualStartTime]+ GETDATE() - GETUTCDATE()) between 1000 and 1239 then '4pmTo8pm'
WHEN DATEDIFF(minute,CAST([ActualStartTime]+ GETDATE() - GETUTCDATE() as DATE),[ActualStartTime]+ GETDATE() - GETUTCDATE()) between 1240 and 1480 then '8pmToMidnight'
else NULL
end TimeFrame
--Quote me
September 30, 2013 at 6:34 pm
The only thing I would say now is, to tidy it up a bit, use a cross apply and some integer division to break up the buckets...
something like this:
select
ActualStartTime,
CASE X.sixth
WHEN 0 then 'MidnightTo4am'
WHEN 1 then '4amTo8am'
WHEN 2 then '8amToNOON'
WHEN 3 then 'NOON_To4pm'
WHEN 4 then '4pmTo8pm'
WHEN 5 then '8pmToMidnight'
ELSE NULL
END AS TimeFrame
from dates
cross apply (
select GETDATE() - GETUTCDATE()
) as T(offset)
cross apply (
select DATEDIFF( minute
, CAST([ActualStartTime] + T.offset as DATE)
, [ActualStartTime] + T.offset
) / 240
) AS X(sixth)
But that is purely down to style preference 🙂
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);October 1, 2013 at 6:10 pm
There's about a 50-50 chance that this article may give you some ideas how you could do this:
http://www.sqlservercentral.com/articles/Excel/91179/
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 2, 2013 at 6:29 am
Dwain, thanks for the article. It's about creating histograms using SQL, which is exactly what I am trying to do ...
--Quote me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply