如何使用新条目复制行

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

我有一个SQL表,有ID,开始和结束日期。例,

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:

根据10月至9月的FY日历,我可以从日期(例如,2010年和2015年的ID 123)获得FY。但是,我想用最初和最后一个FY复制该行;以及中间的财政年度。以下是我希望从上面的数据行中获得的内容:

ID FY

123 2010

123 2011

123 2012

123 2014

123 2015

456 2013

456 2014

3 个解决方案

#1

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

下面的查询使用递归CTE来计算从开始日期会计年度到结束日期会计年度的年份。

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

您可以使用递归cte获取所有可能年份的列表,然后加入到该:

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

你需要一个数字列表。一个方便的方法是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;

#1