I am trying to get a count of the number of suggestions that are 1 week late (>0 and <=7 days). I have an inner SubQuery with a column that returns a 1 if the number of days late falls in this range. This part works fine. There are about 3k total suggestions, a few hundred fall in this time frame.
Here is my calculation (it works fine):
(case when convert(int,datediff(day, SugPoDtl.OrderByDate,constants.today)) > 7 then 0 else (case when convert(int,datediff(day, SugPoDtl.OrderByDate,constants.today)) > 0 then 1 else 0 end) end)
In my top level SubQuery, I group buy the BuyerID and my only calculated field is set up to sum my field that has a 1 if the suggestion is within the 0-7 day range and 0 if not. The problem is I get a total for a buyer that is in the millions. My calculation in the to level SubQuery is simply:
count(SubQuery1.Calculated_WeekLate)
I group by BuyerId. For one buyer, the result is 11,560,464. The total number of suggestions in my inner subquery is only 3,000 so for one buyer to have over 11 million is ridiculous.
Any suggestions will be greatly appreciated. Thanks!
You’re counting all of the records currently, but your formula provides a 1 if it’s in the range. Summarize those to get the “count”.
This looks more accurate.
I re-read your initial post and 9,000+ suggestions didn’t make sense on my end, either. It was just divided better, so at first glance it looked ok.
Getting closer. Two buyers show 0 which is correct. The one still shows 18,670 when there are only 3,099 total. The number for that buyer should be 100-200.
