I have the following query which returns data for 12 months. Originally the query was for 14 days and I changed the day to month in datediff method to get 14 months data but I am getting only 12 months data. Can anyone please check and see why?
Select 'playing' As activity
--,ad.xDate
,min(ad.xDate) As xDate
,Isnull(sum(t.TimePerDay),0) As TimePerDay
From AllDates As ad With (Nolock)
Left Join @test As t On ad.xDate = t.date
GROUP BY datepart(Month, ad.xDate)
--ORDER BY YEAR(datepart(Month, ad.xDate)) DESC, MONTH(datepart(Month, ad.xDate)) DESC, DAY(datepart(Month, ad.xDate))
ORDER BY MIN(ad.xDate)
option (maxrecursion 0)
END
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 things:
Select @MaxDate = @MaxDate
,@MinDate = dateadd(Month, (@LastXMonths + 1), @MaxDate)
@LastXMonths = -14 so your @MinDate is only 13 months before @MaxDate.
Next, this statement:
GROUP BY datepart(Month, ad.xDate)
It will return a number from 1 to 12 so you will never get any more than 12 months.
Solution:
Remove the +1 in the first statement and change the final select to:
Select 'playing' As activity
,ad.xDate
,Isnull(sum(t.TimePerDay),0) As TimePerDay
From AllDates As ad With (Nolock)
Left Join @test As t On ad.xDate = t.date
GROUP BY ad.xDate
ORDER BY ad.xDate
option (maxrecursion 0)
