Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
376 views
in Technique[技术] by (71.8m points)

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

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...