Hourly Sum of Data

• madcloud97

  SSC Enthusiast

  Points: 171

  More actions

  September 6, 2017 at 9:54 pm

  #1958480

  ChrisM@Work - Wednesday, September 6, 2017 8:17 AM

  madcloud97 - Wednesday, September 6, 2017 2:00 AM

  ChrisM@Work - Wednesday, September 6, 2017 1:30 AM

  madcloud97 - Tuesday, September 5, 2017 6:18 PM

  Sorry for the data error, @Chris.
  I will double check again the sample data I posted.

  Edited note: OP updated. I'm very sorry for the data error.

  That's okay, we all make mistakes! What's important is that the error is fixed so folks can work with a predictable data set.
  Holler when you're done, please make it very clear which set of data is the correct one to use, and someone will finish this off for you.

  Thanks for the guidelines and suggestions.

  Using the sample data, I get unexpected results for three rows:

  REC_ID = 11 Should REC_DATETIME be '2017-04-21 08:20:38.000'?

  REC_ID = 78 Should SES_NOTE be 'A000000001'?

  REC_ID = 86 Should REC_DATETIME be '2017-04-21 16:05:38.000'?

  If you can answer these, we can probably wrap this up.

  1. REC_ID = 11 Should REC_DATETIME be '2017-04-21 08:20:38.000'? Yes

  2. REC_ID = 78 Should SES_NOTE be 'A000000001'? Yes **

  3. REC_ID = 86 Should REC_DATETIME be '2017-04-21 16:05:38.000'? Yes

  **Another silly mistake & my clumsiness. I copy the wrong edited content when update OP. The subsequent SES_NOTE (start from REC_ID = 79) should be "A000000079" & for MST_SESSION_TEST 2nd session should be "A000000079".

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 7, 2017 at 9:12 am

  #1958586

  With those three minor data corrections, this query appears to work:

  ;WITH Headers AS ( -- 18 rows
 SELECT *
 FROM #MST_SESSION_TEST p
 CROSS APPLY (
  SELECT TOP(1+DATEDIFF(HOUR,SES_START,SES_END))
   HourStart = DATEADD(HOUR,n,m.SessionStart),
   HourEnd = DATEADD(HOUR,n+1,m.SessionStart)
  FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) d (n)
  CROSS APPLY (SELECT SessionStart = DATEADD(MINUTE,DATEDIFF(MINUTE,'20000101',SES_START),'20000101')) m
 ) x
)
SELECT h.*, '#' '#', x.*
FROM Headers h
OUTER APPLY (
 SELECT REC_PRODUCT, SUM_REC_TOTAL = SUM(REC_TOTAL)
 FROM #MST_REC_TEST c
 WHERE c.SES_NOTE = h.SES_NOTE
  AND c.REC_DATETIME >= HourStart 
  AND c.REC_DATETIME < HourEnd
 GROUP BY REC_PRODUCT
) x

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden