Thomas Franz wrote:
we are saying, that #1 and #3 are correct, not #3 and #4.
LEAD with descending order is the same as LAG with ascending order (except that the returned rows are (usually - if it does not go parallel) are sorted ascending / descending.
Ah... my apologies. I'll have to blame it on insignificant levels of caffeine at the time. Thank you for the correction.
I agree that answer #1 also works. It's also interesting what the differences in execution plans are for larger amounts of data although SET STATICS TIME,IO ON show little difference. The following code is what I used to create a lot more data on my laptop if you want to play. Of course, it's for performance testing only because it's seriously not something someone would actually need to do simply because of the full-monty range of dates that I used for the test.
DROP TABLE IF EXISTS #MonthSales;
WITH cteGenDate AS
SELECT SomeDate = DATEADD(mm,t.N,CONVERT(Date,'00010101'))
FROM dbo.fnTally(0,DATEDIFF(mm,'00010101','99991231')) t
SELECT SaleYear = DATEPART(yy,SomeDate)
,SaleMonth = DATEPART(mm,SomeDate)
,CurrMonthSales = ABS(CHECKSUM(NEWID())%1000)
The code from answer 1 removes one sort (as expected) but the sort then spills to TempDB whereas the code from answer 3 does not.
This was interesting... thanks. you all.