March 15, 2019 at 11:15 am
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
March 15, 2019 at 11:43 am
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
March 15, 2019 at 11:48 am
But I want to use a recursive CTE here.. rather than In clause. This is because, grouping will be per date field.
March 15, 2019 at 11:54 am
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
March 15, 2019 at 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 21, 2019 at 4:53 am
drew.allen - Friday, March 15, 2019 12:26 PMYou'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?
March 21, 2019 at 6:24 am
Sachy123 - Thursday, March 21, 2019 4:53 AMdrew.allen - Friday, March 15, 2019 12:26 PMYou'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
Change is inevitable... Change for the better is not.
March 21, 2019 at 8:28 am
Sachy123 - Thursday, March 21, 2019 4:53 AMdrew.allen - Friday, March 15, 2019 12:26 PMYou'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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy