Using recursive CTE

  • 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

  • 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

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

  • 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

  • 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

  • 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?

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

  • 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

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

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