Using recursive CTE

  • Sachy123

    Grasshopper

    Points: 14

    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

  • jonathan.crawford

    SSCertifiable

    Points: 6347

    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

    Grasshopper

    Points: 14

    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: 6347

    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: 76458

    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

    Grasshopper

    Points: 14

    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: 993884

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • drew.allen

    SSC Guru

    Points: 76458

    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 8 (of 8 total)

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