Usage of CTE - Trick with Dates

Question

Usage of CTE - Trick with Dates

Viewing 9 posts - 61 through 68 (of 68 total)

You must be logged in to reply to this topic. Login to reply

SoCal_DBD Hall of Fame Points: 3383 More actions · Answer 1

Jeff Moden - Friday, March 3, 2017 6:07 PM
SoCal_DBD - Friday, March 3, 2017 3:21 PM
Dan Kelley - Friday, March 3, 2017 2:16 PM
I figured I'd give it a go before I looked at the answers and here's what I came up with. :laugh: It's probably terribly inefficient, I'm new at this and didn't know about tally tables or CTE's 🙂
DECLARE @TotalRowcount INT = (SELECT SUM(Month_Count) FROM abc) DECLARE @abc_row INT = 1 DECLARE @Payment DECIMAL(10,2) = (SELECT Payment FROM abc WHERE SeqNo = @abc_row) DECLARE @MonthsToAdd INT = 0
WHILE (SELECT COUNT(*) FROM def) < @TotalRowcount
BEGIN
IF @MonthsToAdd = (SELECT Month_Count FROM abc WHERE SeqNo = @abc_row)
   BEGIN
  SET @abc_row += 1;
  SET @MonthsToAdd = 0;
  SET @Payment = (SELECT Payment FROM abc WHERE SeqNo = @abc_row);
   END;

INSERT INTO def (SeqNo,Date_Field,Payment)
SELECT @abc_row SeqNo
     ,DATEADD(M, @MonthsToAdd, abc.Date_Field) Date_Field
     ,@Payment Payment
FROM abc
WHERE SeqNo = @abc_row;

SET @MonthsToAdd += 1;
END;
Hi Dan and welcome to this (awesome) forum! Good stab, but that still is operating as a cursor, or more affectionately known as "RBAR" (row by agonizing row). I'll save you the time looking through the responses on this post (I see there are many pages and it is rather old), but here is a solution that implements a "tally table". These things are magical. I can highly recommend reading up on anything written by author extraordinaire on this forum, Jeff Moden. His article on the tally table will blow your mind and can be found here: http://www.sqlservercentral.com/articles/T-SQL/62867/
I took the example and created temp tables to do the work, but here is an example of a tally table solution for this:
Happy coding!
Lisa
There's no need for the first SELECT of the UNION ALL nor the UNION ALL, Lisa. Give it a shot.

Awww Jeff..... I hang my head in SHAME!!! :crying: Original post revised as I don't want that out there for eternity..... Do I get a pass for doing it late on a Friday when I had weekend-itis??

Dan Kelley SSC Enthusiast Points: 130 More actions · Answer 2

SoCal_DBD - Monday, March 6, 2017 8:58 AM
Jeff Moden - Friday, March 3, 2017 6:07 PM
There's no need for the first SELECT of the UNION ALL nor the UNION ALL, Lisa. Give it a shot.
Awww Jeff..... I hang my head in SHAME!!! :crying: Original post revised as I don't want that out there for eternity..... Do I get a pass for doing it late on a Friday when I had weekend-itis??

Thank you Jeff and Lisa for the awesome tips! Lisa, in your revised post, is there any reason why you couldn't (or shouldn't) just change the small tally table seed to 0 instead of subtracting 1 in the DATEADD function? I know it's probably a petty difference, but I'm curious 🙂

IF OBJECT_ID('tempdb..#tally', 'U') IS NOT NULL DROP TABLE #tally

-- Create a temp table version of a small tally table

SELECT TOP 1000 IDENTITY(INTEGER, 0, 1) AS N INTO #Tally FROM sys.[objects] CROSS JOIN sys.columns

INSERT INTO #def (SeqNo, Date_Field, Payment) SELECT a.SeqNo, DATEADD(M, t.N, a.Date_Field) Date_Field, a.Payment FROM #abc a CROSS JOIN #Tally t WHERE t.N < a.Month_Count

SELECT * FROM #def ORDER BY SeqNo, Date_Field

Also, my apologies for reviving a two year old thread! As you said, it was Friday evening so it's understandable. :laugh:

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

SoCal_DBD - Monday, March 6, 2017 8:58 AM
Awww Jeff..... I hang my head in SHAME!!! :crying: Original post revised as I don't want that out there for eternity..... Do I get a pass for doing it late on a Friday when I had weekend-itis??

Anyone who tries to help someone else, especially with weekend-itis looming, is aces in my book. 😉

--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)

EliG SSC Veteran Points: 267 More actions · Answer 4

Interesting read - greatly appreciated.
One point to note: since the second query in the CTE (after the UNION ALL) already limits the entries to be within range, there is no need for the second where clause (in the INSERT statement), as dates which fall out of that range have already been eliminated.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

EliG - Monday, March 6, 2017 11:56 AM
Interesting read - greatly appreciated.
One point to note: since the second query in the CTE (after the UNION ALL) already limits the entries to be within range, there is no need for the second where clause (in the INSERT statement), as dates which fall out of that range have already been eliminated.

Which code are you referring to? I ask because there's no need for a CTE and there's no need for a UNION ALL. Heh... nor is there a need for the WHILE. 😉

--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)

EliG SSC Veteran Points: 267 More actions · Answer 6

EliG

SSC Veteran

Points: 267

March 6, 2017 at 12:20 pm

#1931902

The original article.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

EliG - Monday, March 6, 2017 12:20 PM
The original article.

I'd recommend avoiding the rCTE in the original article. Please refer to the following article, which shows just how bad rCTEs that calculate a sequence actually are compared to 3 other simpler methods.
http://www.sqlservercentral.com/articles/T-SQL/74118/

--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)

EliG SSC Veteran Points: 267 More actions · Answer 8

Jeff Moden - Monday, March 6, 2017 12:30 PM
EliG - Monday, March 6, 2017 12:20 PM
The original article.
I'd recommend avoiding the rCTE in the original article. Please refer to the following article, which shows just how bad rCTEs that calculate a sequence actually are compared to 3 other simpler methods.
http://www.sqlservercentral.com/articles/T-SQL/74118/

Jeff,

My point wasn't whether or not to use an rCTE, rather, it was a point made on an extraneous piece of processing contained in an article written to demonstrate a method of reducing processing.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

EliG - Monday, March 6, 2017 12:57 PM
Jeff Moden - Monday, March 6, 2017 12:30 PM
EliG - Monday, March 6, 2017 12:20 PM
The original article.
I'd recommend avoiding the rCTE in the original article. Please refer to the following article, which shows just how bad rCTEs that calculate a sequence actually are compared to 3 other simpler methods.
http://www.sqlservercentral.com/articles/T-SQL/74118/
Jeff,
My point wasn't whether or not to use an rCTE, rather, it was a point made on an extraneous piece of processing contained in an article written to demonstrate a method of reducing processing.

Understood. My point was that your point is a bit moot because you shouldn't be using the rCTE method.

--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)