Finding the last month's values
-
Comments posted to this topic are about the item Finding the last month's values
-
As far as I can tell, there are actually two correct answers to this one. Both
LEAD(currMonthSales) OVER (ORDER BY saleyear DESC, salemonth DESC) AS prevSales
and
LAG(currMonthSales) OVER (ORDER BY saleyear ASC, salemonth ASC) AS prevSales
yield correct results, although the LAG version is possibly more inutitive.
Just because you're right doesn't mean everybody else is wrong. -
I agree with Rune Bivrin's comment. There are two correct answers, as far as I can tell.
--> Steve Jones: If we are incorrect, please provide test data that clearly demonstrates that we are not getting the right answer with the first choice.
Thanks.
-
This was removed by the editor as SPAM
-
BAWAAA-HAAAA-HAAAA!!! I'll get the popcorn! 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok... so to those that are saying that answer #4 is also correct, that's incorrect. Here's the output from answer #3 and answer #4. #4 is incorrect if you pay attention to where the NULL appears in the output. Also, the PrevSales is coming from the wrong month for the non-null entries if you accept the normal definition of "PrevSales".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.
God is real, unless declared integer.
-
First choice works, too - LEAD...
- 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)
INTO #MonthSales
FROM cteGenDate
;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.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Cut/paste strikes again. I edited the wrong item and made #1 correct. I've changed it to be incorrect.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply