Saif Saif - 3 months ago 11
SQL Question

GroupBy Datepart function missing data

So I have a query that I'm trying to find sums and averages while grouping by the timestamp down to the minute. However, if the timestamp has the SAME minute but in a different hour, that rows data doesn't show up. Below is my query and the output and what the individual rows are:

NumCardsPassed ProcessingTime ProcessingDate
10 4 2016-08-29 11:13:44.000
1 0 2016-08-29 11:49:43.000
2 1 2016-08-29 12:19:42.000


and here are the individual rows with out all the grouping. Notice how the timestamp of 12:13 and 22:13 is missing in the above query (NumCardsPassed above should equal 10 instead, it added it to 11:13 numcards passed at 10)

NumCardsPassed ProcessingTime processingdate
1 2 2016-08-29 11:13:44.000
1 0 2016-08-29 11:49:43.000
8 10 2016-08-29 12:13:44.000
1 3 2016-08-29 12:19:42.000
1 0 2016-08-29 22:13:47.000

Select sum(numcardspassed) as "NumCardsPassed",
avg(processingtime) as "ProcessingTime", min(ProcessingDate) as "ProcessingDate"
From orderprocessormetrics
where ProcessingDate >= '8/27/2016' and ProcessingDate < '8/30/2016' and PreprocessorType = 'SOP' and numcardsPassed > 0 and clientid = 6820
Group by DatePart(minute, orderprocessormetrics.processingdate)
order by processingdate


So basically since that datePart is based on minutes, it's grouping all the rows with the same minutes together regardless of hours. Is there a way to take into account hours and date and not just minutes. I still need it to group by minutes and not seconds or milliseconds.

Answer

Try this (nice name btw lol) :

Select  sum(numcardspassed) as "NumCardsPassed", sum(numcardsfailed) as "NumCardsFailed",  
avg(processingtime) as "ProcessingTime",  min(ProcessingDate) as "ProcessingDate" 
From orderprocessormetrics
where ProcessingDate >= '8/27/2016' and ProcessingDate < '8/30/2016' and PreprocessorType = 'SOP' and numcardsPassed > 0 and clientid = 6820
Group by  convert(varchar, ProcessingDate, 108) 
order by processingdate
Comments