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.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)