Using recursive CTE

Question

Using recursive CTE

Sachy123

Valued Member

Points: 64
More actions
March 15, 2019 at 11:15 am

#409702

I have two queries
Query 1 : Retrieves last two end of month date from Date Dimension output as
ActualDate 28-02-2019 30-01-2019
Query 2 : Uses a Date field in a where clause...
E.g. All the data at Date D1.
How can I use the output of query 1 recursively in query 2 so that
The combined output should be
Select * from Query2 where ActualDate= "28-02-2019" Union all All Select * from Query2 where ActualDate= "30-01-2019" (Date should come from the Query1)
Any help would be much appreciated.
Query 2 is as below:
SELECT SL.InvoiceID, sum(SL.Balance) AS Balance,FROM FactableBalance AS SLINNER JOIN DateDimension AS DT1 ON DT1.ActualDate = '2019-02-28 00:00:00.000'WHERE SL.LedgerAccountType = 'Credit' AND DT1.ID >= BalanceOpenDateID AND DT1.ID < BalanceCloseDateIDGROUP BY SL.InvoiceID

Viewing 8 posts - 1 through 7 (of 7 total)

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

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 1

SELECT <thing>
WHERE date IN ( Query1 - make sure it only returns the date values)

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Sachy123 Valued Member Points: 64 More actions · Answer 2

But I want to use a recursive CTE here.. rather than In clause. This is because, grouping will be per date field.

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 3

you don't have to use recursion to do the grouping. Still put your aggregates in your SELECT, add a GROUP BY date clause to the end, problem solved

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

drew.allen SSC Guru Points: 76987 More actions · Answer 4

You're also confused about the meaning of recursion. Recursion is when one CTE refers to itself, not when one query references another. Even if you use a CTE, I see no reason to make it recursive.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Sachy123 Valued Member Points: 64 More actions · Answer 5

drew.allen - Friday, March 15, 2019 12:26 PM
You're also confused about the meaning of recursion. Recursion is when one CTE refers to itself, not when one query references another. Even if you use a CTE, I see no reason to make it recursive.
Drew

but I would like it to dynamically take one value of date from Query 1 and then append the results .. I thought CTE can help.
IHow can I then reform my query? use "in" clause?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Sachy123 - Thursday, March 21, 2019 4:53 AM
drew.allen - Friday, March 15, 2019 12:26 PM
You're also confused about the meaning of recursion. Recursion is when one CTE refers to itself, not when one query references another. Even if you use a CTE, I see no reason to make it recursive.
Drew
but I would like it to dynamically take one value of date from Query 1 and then append the results .. I thought CTE can help.
IHow can I then reform my query? use "in" clause?

I suggest you post the actual queries so that people can help you.

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

drew.allen SSC Guru Points: 76987 More actions · Answer 7

Sachy123 - Thursday, March 21, 2019 4:53 AM
drew.allen - Friday, March 15, 2019 12:26 PM
You're also confused about the meaning of recursion. Recursion is when one CTE refers to itself, not when one query references another. Even if you use a CTE, I see no reason to make it recursive.
Drew
but I would like it to dynamically take one value of date from Query 1 and then append the results .. I thought CTE can help.
IHow can I then reform my query? use "in" clause?

A CTE might help. I never said it wouldn't. I said that I didn't see a need for a RECURSIVE CTE, which is a specific type of CTE.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA