Saif Saif - 3 months ago 13
MySQL Question

SSRS take average IF value of another field is equal

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

Answer

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.

Comments