在两个日期之间将一行记录拆分为多行

Question

有人能帮我做这件事吗：

我的表格数据类似

Datefiled       Field1     Field2    Field2   Field4
01-01-2012     ABC         10        15         17
01-04-2013     PQR          7        80         65
01-05-2014     XYZ         15        25         77

输出看起来像这个

Month/Year       Field1     Field2    Field2   Field4
01/2012            ABC         10        15         17
02/2012            ABC         10        15         17
03/2012            ABC         10        15         17
04/2012            ABC         10        15         17
.....
03/2013           ABC         10         15         17
04/2013           PQR          7          80         65
05/2013           PQR          7          80         65
06/2013           PQR          7          80         65
07/2013           PQR          7          80         65
.......
04/2014           PQR          7          80         65
05/2014           XYZ         15          25         77

Answer 1

只需创建一个日历表，每天一行或每月一行，就可以使用计数表（=虚拟，例如用CTE构建），并执行以下操作：

select
  *
from
  calendar c
  cross apply (
    select top 1 *
    from yourtable y
    where y.Datefiled < c.calendardate
    order by Datefiled desc
  ) y

Answer 2

;WITH datat as (
SELECT *
FROM (VALUES
('2012-01-01', 'ABC', 10, 15, 17),('2012-12-12', 'LKJ', 16, 12, 13),
('2013-01-15', 'CVB', 16, 11, 19),('2013-04-01', 'PQR',  7, 80, 65),
('2014-04-24', 'XYZ', 15, 25, 77),('2014-05-17', 'DEF', 12, 40, 50)
) as t(Datefiled, Field1, Field2, Field3, Field4)
), cte AS (
SELECT CAST(MIN(Datefiled) AS DATE) as date_,
       CAST(MAX(Datefiled) AS DATE) AS maxd
FROM datat
UNION ALL
SELECT DATEADD(day,1,date_), maxd
FROM cte 
WHERE date_ < maxd
)
SELECT CAST([Month] as nvarchar(2)) + '/' + CAST([Year] as nvarchar(4)) as [Month/Year],
        Field1,
        Field2,
        Field3,
        Field4  
FROM (
SELECT DISTINCT  
        MONTH(date_) as [Month],
        YEAR(date_) as [Year],
        Field1,
        Field2,
        Field3,
        Field4
FROM cte c
LEFT JOIN (
            SELECT TOP 1 WITH TIES  CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, d1.Datefiled ), 0) as date) as [DateStart],
                                    CAST(DATEADD(MILLISECOND, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, d2.Datefiled), 0)) as date) as [DateEnd],
                                    d1.Field1,
                                    d1.Field2,
                                    d1.Field3,
                                    d1.Field4
            FROM datat d1
            LEFT JOIN datat d2
                ON d1.Datefiled < d2.Datefiled
            ORDER BY ROW_NUMBER() OVER (PARTITION BY d1.Datefiled ORDER BY d2.Datefiled)
                ) as d
        ON c.date_ between d.[DateStart] and ISNULL(d.[DateEnd],c.date_)
        WHERE Field1 IS NOT NULL
        ) as t 
    ORDER BY [Year], [Month]
OPTION (MAXRECURSION 0)

输出：

Month/Year Field1 Field2      Field3      Field4
---------- ------ ----------- ----------- -----------
1/2012     ABC    10          15          17
2/2012     ABC    10          15          17
3/2012     ABC    10          15          17
4/2012     ABC    10          15          17
5/2012     ABC    10          15          17
6/2012     ABC    10          15          17
7/2012     ABC    10          15          17
8/2012     ABC    10          15          17
9/2012     ABC    10          15          17
10/2012    ABC    10          15          17
11/2012    ABC    10          15          17
12/2012    LKJ    16          12          13
1/2013     CVB    16          11          19
2/2013     CVB    16          11          19
3/2013     CVB    16          11          19
4/2013     PQR    7           80          65
5/2013     PQR    7           80          65
6/2013     PQR    7           80          65
7/2013     PQR    7           80          65
8/2013     PQR    7           80          65
9/2013     PQR    7           80          65
10/2013    PQR    7           80          65
11/2013    PQR    7           80          65
12/2013    PQR    7           80          65
1/2014     PQR    7           80          65
2/2014     PQR    7           80          65
3/2014     PQR    7           80          65
4/2014     XYZ    15          25          77
5/2014     DEF    12          40          50
(29 row(s) affected)