Nesting a CTE Expression in TSQL

Question

I have a TSQL recursive CTE that generates a date range between two dates as follows:

Select
    Cast('2020-05-01' As DATETIME) 'DATE'
Union All
Select
    DateAdd(dd, 1, t.DATE)
From
    DATE_RANGE t
Where
    DateAdd(dd, 1, t.DATE) <= '2020-05-02'

That works fine, so executing that gives me two records as below:

01/05/2020
02/05/2020

Then I use that in another expression along with a second table, PEOPLE_RECORDS:

With
    DATE_RANGE As (
     Select
         Cast('2020-05-01' As DATETIME) 'DATE'
     Union All
     Select
         DateAdd(dd, 1, t.DATE)
     From
         DATE_RANGE t
     Where
         DateAdd(dd, 1, t.DATE) <= '2020-05-02'
    )
Select
    *
From
    DATE_RANGE,
    PEOPLE

I have deliberately not linked the two tables, because I need the two dates (or however many date records are in my chosen range) to duplicate for every record in my PEOPLE table.

So, if I have 20 records in PEOPLE and a date range of 2 days I would expect 40 records from this query. This also works fine, just thought it might help to give some background about what I am trying to achieve :)

Next I want to be able to package this up in a sub query and link to a third CONTACT table via a left outer join so I can count how many records exist in the third CONTACT table for every PEOPLE record for each date in the recursive CTE range. The left outer join is so I can still see the dates where no CONTACT records exist for each PEOPLE record.

I have tried nesting the whole thing inside another WITH query and also a SELECT query, but I am aware that can't be done from looking at other posts on Stack Overflow.

What I am not aware of is how to get what I need. Hopefully this explains what I am trying to achieve, sorry if it's confusing. I am also aware that there could be a completely different method of getting what I am after, so please feel free to suggest alternatives. One thing to bear in mind is that I am querying with read-only permissions, and cannot create functions or procedures to do this.

user_978528 · Answer

Similar to what JMabee was saying, but without functions. Will something like this work?

With
    DATE_RANGE As (
     Select
         Cast('2020-05-01' As DATETIME) 'DATE'
     Union All
     Select
         DateAdd(dd, 1, t.DATE)
     From
         DATE_RANGE t
     Where
         DateAdd(dd, 1, t.DATE) <= '2020-05-02'
    )
, people_and_dates as ( 
    Select
        *
    From
        DATE_RANGE,
        PEOPLE
)
select * 
from people_and_dates
-- do other stuff here

user_8126219 · Answer

It sounds like you want a TVF that you can then JOIN against. Consider this:

CREATE FUNCTION getPeopleDates(
    @dtStart datetime,
    @dtEnd datetime
    )
    RETURNS TABLE
    AS
    RETURN
    With
        DATE_RANGE As (
         Select
             @dtStart 'DATE'
         Union All
         Select
             DateAdd(dd, 1, t.DATE)
         From
             DATE_RANGE t
         Where
             DateAdd(dd, 1, t.DATE) <= @dtEnd
        )
    Select
        *
    From
        DATE_RANGE
        CROSS JOIN People

Given I don't have your table definitions I cannot limit the columns the function returns, but you can narrow that down on your own. You could then join against this such as:

SELECT t2.* 
    FROM getPeopleDates('5/1/20', '5/2/20') t1
    LEFT JOIN SomeTable t2 on t1.PersonID = t2.PersonID 
WHERE  t2.PersonID IS NULL

Nesting a CTE Expression in TSQL

2 Answers