Retrieve information dynamically from multiple CTE

Question

I have multiple CTEs and I want to retrieve some information from a couple of them into next CTE.

So, I have this information from one of the CTEs:

PeriodID  StarDate
1         2006-01-01
2         2007-04-25
3         2008-08-16
4         2009-12-08
5         2011-04-017

and this from other:

RecordID  Date
100       2007-04-15
101       2008-05-21
102       2008-06-06
103       2008-07-01
104       2009-11-12

And I need to show in next one:

RecordID  Date        PeriodID
100       2007-04-15  1
101       2008-05-21  2
102       2008-06-06  2
103       2008-07-01  2
104       2009-11-12  3

I can use some case/when statement to define if date of record is in period 1,2,3,4 or 5 but it some situation I can have different numbers of periods return from the first CTE.

Is there a way to do this in the above context?

podiluska podiluska · Accepted Answer · 2012-10-22T10:34:14

You can have multiple CTEs defined as follows, and then select from and join them as you would any other table.

with cte1 as (select * ...),
cte2 as (select * ...)
    select 
        cte2.*, 
        periodid 
    from cte2
cross apply 
        (select top 1 * from cte1 where cte2.recorddate> cte1.startdate order by startdate desc) v

Retrieve information dynamically from multiple CTE

1 Answers