Multiple CTEs Join with Other Tables

Multiple CTEs Join with Other Tables

1. Create  1st CTE : cte_year

2.Create 2nd CTE: cte_date

3. Use Joining Table at Left Side :Job_Plan

Example

WITH cte_year

AS (
        SELECT year(getdate()) AS current_year,1 AS cnt

        UNION ALL

        SELECT current_year + 1 AS current_year,cnt + 1 AS cnt FROM cte_year WHERE cnt < 5

        )

,cte_date

AS (

        SELECT CASE
                       WHEN month(getdate()) >= 7  THEN year(getdate())
                       ELSE year(dateadd(yy, - 1, getdate()))
                       END start_dt

               ,CASE
                       WHEN month(getdate()) <= 6 THEN year(getdate())
                       ELSE year(dateadd(yy, 1, getdate()))
                       END end_dt
               ,1 AS cnt
   

        UNION ALL

        SELECT start_dt + 1 start_dt ,end_dt + 1 end_dt ,cnt + 1 FROM cte_date  WHERE cnt < 5

        )

SELECT DISTINCT A.* FROM Job_Plan(NOLOCK) a

INNER JOIN (

        SELECT cast(current_year AS VARCHAR(100)) AS Plan_Name  FROM cte_year

        UNION ALL

        SELECT cast(right(current_year, 2) + 'FS' AS VARCHAR(100)) AS Plan_Name  FROM cte_year

        UNION ALL

        SELECT cast(right(current_year, 2) + 'PS' AS VARCHAR(100)) AS Plan_Name  FROM cte_year

        UNION ALL

        SELECT cast(right(start_dt, 2) + '-' + right(end_dt, 2) AS VARCHAR(100)) AS Plan_Name   FROM cte_date

        UNION ALL

        SELECT cast(right(start_dt, 2) + ' - ' + right(end_dt, 2) AS VARCHAR(100)) AS Plan_Name   FROM cte_date

        ) F ON ltrim(rtrim(a.Plan_Name_Long)) LIKE '%' + ltrim(rtrim(F.Plan_Name)) + '%'

        AND a.Plan_Event_Name_Long NOT LIKE '%' + cast(year(dateadd(yy, - 1, getdate())) AS VARCHAR(100)) + '%'

        AND Plan_ID NOT IN (  SELECT Plan_ID    FROM Job_In_Plan(NOLOCK)  )

ORDER BY a.Plan_ID


output of cte_year
current_year cnt
2013        1
2014        2
2015        3
2016        4

2017        5


output of cte_date
start_dt end_dt cnt
2013 2014 1
2014 2015 2
2015 2016 3
2016 2017 4

2017 2018 5