问题:

I have a SQL table that have ID , start and End Dates. Example,

ID   StartDt    EndDt
123  1/1/2010   12/31/2014 
456  7/16/2013  11/20/2014

Based on Oct-Sept FY calendar I can get the FY from the dates (e.g., 2010 and 2015 for ID 123). However, I would like to duplicate the row with the initial and last FY; and the in-between FY. Below is what I would like to have from the above rows of data:

ID FY

123 2010

123 2011

123 2012

123 2014

123 2015

456 2013

456 2014

回答1:

The query below uses a recursive CTE to count years from the start date fiscal year to the end date fiscal year.

;WITH CTE AS (
    SELECT
        ID,
        YEAR(StartDt) AS FY,
        YEAR(EndDt) AS EY
    FROM [Source]
    UNION ALL
    SELECT 
        ID,
        FY + 1,
        EY
    FROM CTE
    WHERE FY < EY
)
    SELECT ID, FY FROM CTE ORDER BY ID, FY

回答2:

You can use a recursive cte to get a list of all possible years, then JOIN to that:

;with cte AS (SELECT 2010 AS Yr
              UNION ALL
              SELECT Yr + 1
              FROM cte
              WHERE Yr < 2015)
SELECT a.ID, b.Yr
FROM YourTable a
JOIN cte b
  ON b.Yr BETWEEN YEAR(a.StartDt) AND YEAR(a.EndDt)

回答3:

You need a list of numbers. A convenient way is master..spt_values:

with n as (
      select row_number() over (order by (select NULL)) - 1 as n
      from master..spt_values
     )
select id, year(t.startDt) + n.n as fy
from table t join
     n
     on dateadd(year, n.n, t.startDt) <= t.endDt;