[Solved] Why the following SQL Server query returns 12 months data and not 14 months data

by


You need to group by both month and year parts of ad.xDate. There are only twelve months of the year and you should be seeing the earliest two (14 – 2) month of your results with totals that are too large because they actually represent a combination of two calendar months.

It worked in your original version because there are more than 14 days in any month. If you tried to extend that old query beyond 31 days (or 28, 29, 30 for some months) then you find the same problem all over again.

...
SELECT
    'playing' As activity,
    min(ad.xDate) As xDate, 
    Isnull(sum(t.TimePerDay), 0) As TimePerDay
FROM AllDates As ad Left Outer Join @test As t On ad.xDate = t.date
GROUP BY Year(ad.xDate), Month(ad.xDate) /* <--- change here */
ORDER BY xDate

2

solved Why the following SQL Server query returns 12 months data and not 14 months data