SSRS take average IF value of another field is equal

Question

I have created a performance tracker of one of our systems using SSRS and database queries. So right now, it will sum cards and the processing time and SSRS will chart it using the ProcessingDate per day. Our database puts a new row in the database based on DataBatchID. However, this method of adding all the cards and adding the processingtime per day doesn't correctly take into account situations where threads were running together concurrently. So using SSRS is there a way for it to determine that if the ProcessingDate is exactly the same, to just take those and sum the NumCardsPassed and take the average of ProcessingTime and then add those results to the rest for that day. Sorry if I didn't explain it well. Below is the dataset example returned from the query so for all that came in at 10:24 add the numCardsPassed and average the ProcessingTime and then do the same for 10:25. After that, then graph all those values together for the day of 8/26 Right now, as it's graphing it, it's making it seem like everything is running one at a time in sequential order and NOT together which it sometimes may be doing:

ProcessingDate  DataBatchId NumCardsPassed  ProcessingTime
8/26/2016 7:39    10112       99314               485
8/26/2016 8:51    10113        4971               29
8/26/2016 9:14    10114        4971               34
8/26/2016 10:20   10115        4957               38
8/26/2016 10:23   10116        4961               104
8/26/2016 10:24   10117        4979               144
8/26/2016 10:24   10119        4979               182
8/26/2016 10:24   10118        4982               161
8/26/2016 10:24   10120        4987               219
8/26/2016 10:24   10122        4982               243
8/26/2016 10:24   10121        4981               236
8/26/2016 10:24   10125        4949               262
8/26/2016 10:24   10123        4941               247
8/26/2016 10:24   10127        4973               278
8/26/2016 10:24   10124        4975               241
8/26/2016 10:24   10128        4981               279
8/26/2016 10:25   10129        4976               280
8/26/2016 10:25   10126        4972               258
8/26/2016 10:25   10130        4979               277
8/26/2016 10:25   10132        4976               288
8/26/2016 10:25   10131        4972               277
8/26/2016 10:25   10133        4841               288

score 0 · Accepted Answer · edited May 23 '17 at 12:14

0

You can do that in your query:

SELECT ProcessingDate, SUM(NumCardsPassed) AS NumCardsPassed, AVG(ProcessingTime) AS ProcessingTime
FROM ProcessTable
GROUP BY ProcessingDate
ORDER BY ProcessingDate

Note that if the date is actually a date precise to milliseconds and there are slight variances in the milliseconds then you may need to do some rounding or conversion of the date field to get it to group to the minute properly.

You can round the date to minutes using the following SQL:

MySql (from this answer)

SELECT SEC_TO_TIME((TIME_TO_SEC(ProcessingDate) DIV 60) * 60) AS TimeInMinutes

Sql Server

SELECT DateAdd(mi, DateDiff(mi, 0, ProcessingDate), 0) AS TimeInMinutes

edited May 23 '17 at 12:14

Community

1
1

answered Aug 30 '16 at 00:48

Chris Latta

20,316
4
62
70

that works except for one thing. the processingDate goes down to the milliseconds (00:00:00.000) which is too precise and so only values which are the exact same down to the millisecond is getting grouped together. Is there a way to group by minutes? I tried using convert on the ProcessingDate column but it isn't doing it. – Saif Aug 30 '16 at 16:03
Added rounding sql to answer – Chris Latta Aug 31 '16 at 00:36

SSRS take average IF value of another field is equal

1 Answers1