Need to sort by Date then Hour, then output Date, text Day of week , range of hours SQL Server 2008 R2

Question

NEWBIE at work! I am trying to create a simple summary that counts the number of customer visits and groups by 1) date and 2) hour, BUT outputs this:

 Date          Day of Wk   Hour   #visits
 8/12/2013     Monday      0         5
 8/12/2013     Monday      1         7
 8/12/2013     Monday      6        10
 8/13/2013     Tuesday     14       25
 8/13/2013     Tuesday     16       4

We are on military time, so 14 = 2:00 pm

Select       
    TPM300_PAT_VISIT.adm_ts as [Date]
    ,TPM300_PAT_VISIT.adm_ts as [Day of Week]
,TPM300_PAT_VISIT.adm_ts as [Hour]
,count(TPM300_PAT_VISIT.vst_ext_id) as [Total Visits]
From
    TPM300_PAT_VISIT
Where
    TPM300_PAT_VISIT.adm_srv_cd='22126'
and TPM300_PAT_VISIT.adm_ts between '07-01-2013' and '08-01-2013'
Group by   
    cast(TPM300_PAT_VISIT.adm_ts as DATE)
    ,datepart(weekday,TPM300_PAT_VISIT.adm_ts)
    ,datepart(hour,TPM300_PAT_VISIT.adm_ts)
Order by    
    CAST(TPM300_PAT_VISIT.adm_ts as DATE)
    ,DATEPART(hour,TPM300_PAT_VISIT.adm_ts)

By *military* time you basically mean the **World Standard 24-hour** clock that pretty much the entire world - except for the US - is using, righT? — marc_s, Aug 13 '13 at 14:46
You have shown us the produced result, but what is the result you expect, and how do they differ? — Rikalous, Aug 13 '13 at 14:48
You've shown a query that (whilst rough) looks like it might do the job - can you explain what problem(s) you're having with it? Also, can you tell us what the date ranges are in the middle of the query? Unfortunately, `'07-01-2013'` means different things to different people. — Damien_The_Unbeliever, Aug 13 '13 at 14:49
Yes, I mean a 24-hr clock. Actually what I showed you was what I WANT to produce. This is what I get when I try to run it: Msg 8120, Level 16, State 1, Line 1 Column 'TPM300_PAT_VISIT.adm_ts' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. — user2611375, Aug 13 '13 at 18:37
the date range in the middle is the date the customer visited our facility. July 1, 2013 00:00 through July 31, 2013 11:59:59 is what I'm aiming for. — user2611375, Aug 13 '13 at 20:58

score 0 · Answer 1 · answered Aug 14 '13 at 06:12

This should solve the problem:

; With Streamlined as (
    SELECT
        DATEADD(hour,DATEDIFF(hour,'20010101',adm_ts),'20010101') as RoundedTime,
        vst_ext_id
    from
        TPM300_PAT_VISIT
    where
        adm_srv_cd='22126' and
        adm_ts >= '20130701' and
        adm_ts < '20130801'
)
Select
    CONVERT(date,RoundedTime) as [Date],
    DATEPART(weekday,RoundedTime) as [Day of Week],
    DATEPART(hour,RoundedTime) as [Hour],
    count(vst_ext_id) as [Total Visits]
From
    Streamlined
Group by   
    RoundedTime
Order by    
    CONVERT(date,RoundedTime),
    DATEPART(hour,RoundedTime)

In the CTE (Streamlined)'s select list, we floor each adm_ts value down to the nearest hour using DATEADD/DATEDIFF. This makes the subsequent grouping easier to specify.

We also specify a semi-open interval for the datetime comparisons, which makes sure we include everything in July (including stuff that happened at 23:59:59.997) whilst excluding events that happened at midnight on 1st August. This is frequently the correct type of comparison to use when working with continuous data (floats, datetimes, etc), but means you have to abandon BETWEEN.

I'm also specifying the dates as YYYYMMDD which is a safe, unambiguous format. Your original query could have been interpreted as either January 7th - January 8th or 1st July - 1st August, depending on the settings of whatever account you use to connect to SQL Server. Better yet, if these dates are being supplied by some other (non-SQL) code, would be for them to be passed as datetimes in the first place, to avoid any formatting issues.

Thank you - this is pretty close to what I want for output. Can I put a case statement in that would convert the numeric Day of Week to the Word? i.e. 1 = Sun 2 = Mon 3 = Tue. Would a case statement go in the select clause? — user2611375, Aug 14 '13 at 14:06
@user2611375 - you could use [`DATENAME()`](http://technet.microsoft.com/en-us/library/ms174395.aspx) instead of `DATEPART()` and that would return the full name. It's up to you if you then wrap that in a `SUBSTRING()` or `LEFT()` to reduce it down to 3 characters. — Damien_The_Unbeliever, Aug 14 '13 at 14:17
I got that. One more question - when you use a CASE statement, can you not use "WHEN <7 THEN 'Nights' WHEN >=19 THEN 'Nights' ELSE 'Days' END". I tried that and it gave me an error on the < sign. — user2611375, Aug 14 '13 at 14:55
No. If you want to use conditions, you change from a [simple CASE to a searched CASE](http://technet.microsoft.com/en-us/library/ms181765.aspx). Instead of having `CASE Column WHEN Value THEN` you write `CASE WHEN Column < 7 THEN`, for example - you have to have a complete expression in each `WHEN`, and nothing appears between `CASE` and the first `WHEN`. — Damien_The_Unbeliever, Aug 14 '13 at 14:57
Yes, that worked. Now one more step and I have my perfect output. I want to group the ouput by Date, Day of Week, Hour (which is now ouputting "Nights" or "Days"). Two problems: it's still sorting by the numeric hour so I wind up with 2013-07-01 Mon Nights 1 2013-07-01 Mon Days 3 2013-07-01 Mon Nights 2. What I want is 2013-07-01 Days 3 2013-07-01 Nights 3. Make any sense? — user2611375, Aug 14 '13 at 15:11

Need to sort by Date then Hour, then output Date, text Day of week , range of hours SQL Server 2008 R2

1 Answers1