根据有效日期和从日期开始创建新行

Question

我有一个表，其帐户号、月底有效于列和月底有效。我需要的是一个具有帐户号的表和一个列，该列具有帐户运行时的所有月底日期，包括月底有效日期。当前表如下所示

新表需要像这样

我尝试过使用日历表和CTE表类型查询，但没有成功。

任何帮助都会很好。

Answer 1

这可以使用在语句中使用多个逗号分隔的CTEs来实现。

查询

with t0 (i) AS (select 0 union all select 0 union all select 0 union all select 0 union all select 0 union all select 0),
     t1 (i) AS (select 0 from t0 a inner join t0 b on a.i = b.i), 
     n (i) AS (select row_number()over(order by i)  from t1),
     Account_details (Account_number,valid_from,valid_to,mth,Live_date)As(

     select  Account_number,valid_from,valid_to,   datediff(month,valid_from,valid_to ) mth, valid_from"Live_date" 
     from tbl1
     union all
     select  Account_number,valid_from,valid_to,   datediff(month,valid_from,valid_to ) mth, EOMONTH (dateadd(month,n.i,valid_from)) "Live_date" 
     from tbl1
     inner join n on 1=1 and n.i between 1 and datediff(month,valid_from,valid_to ) 
     )
select * 
from Account_details
where Account_details.Account_number =1
order by  Account_details.Account_number

输出

​

​

CTE表t0、t1和n将生成数字。这是在没有数据的情况下生成行的最佳方法。

然后使用CTE表Account_details从表中提取数据。

Answer 2

基于sql的msdn线程如何获得两个日期之间的月结束日期。

DECLARE @Old AS Table (AccountNumber INT, ValidFrom DATE, ValidTo DATE)
DECLARE @New AS Table  (AccountNumber INT, LiveDate DATE)

INSERT INTO @old
SELECT 1, '20130630', '20131130' UNION ALL
SELECT 2, '20130630', '20131231' UNION ALL
SELECT 3, '20120430', '20120531' UNION ALL
SELECT 4, '20170331', '20171130'

SELECT TOP 100 * FROM @old

DECLARE @AccountNumber INT, @ValidFrom DATE, @ValidTo DATE
DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FOR
    SELECT AccountNumber, ValidFrom, ValidTo
    FROM @old

    OPEN @Cursor

    FETCH NEXT INTO @Cursor FROM @AccountNumber, @ValidFrom, @ValidTo

    WHILE @@FETCH_STATUS = 0    
    BEGIN        
            ;WITH cteEndMonthDates (MonthEndDate)
            AS
            (
                   SELECT eomonth(@ValidFrom) AS MonthEndDate
                   UNION ALL
                   SELECT eomonth( dateadd(day, 1, MonthEndDate)) AS MonthEndDate
                   FROM   cteEndMonthDates
                   WHERE  MonthEndDate  < eomonth(@ValidTo)
            )
            INSERT INTO @new (AccountNumber, LiveDate)
            SELECT @AccountNumber, MonthEndDate
            FROM cteEndMonthDates

        FETCH NEXT FROM @Cursor INTO @AccountNumber, @ValidFrom, @ValidTo    
    END

    CLOSE @Cursor    
    DEALLOCATE @Cursor

SELECT * FROM @New

编辑:或者没有光标

DECLARE @Old AS Table (AccountNumber INT, ValidFrom DATE, ValidTo DATE)
DECLARE @New AS Table  (AccountNumber INT, LiveDate DATE)

INSERT INTO @old
SELECT 1, '20130630', '20131130' UNION ALL
SELECT 2, '20130630', '20131231' UNION ALL
SELECT 3, '20120430', '20120531' UNION ALL
SELECT 4, '20170331', '20171130' UNION ALL
SELECT 5, '20180430', '20190131'  UNION ALL
SELECT 6, '20160430', '20180531' 

SELECT TOP 100 * FROM @old

    ;WITH cteEndMonthDates (AccountNumber, MonthEndDate)
    AS
    (
            SELECT AccountNumber, eomonth(ValidFrom) AS MonthEndDate
            FROM @Old
            UNION ALL
            SELECT x.AccountNumber, eomonth( dateadd(day, 1, MonthEndDate)) AS MonthEndDate
            FROM   cteEndMonthDates x
            JOIN @Old o ON o.AccountNumber = x.AccountNumber
            WHERE  MonthEndDate  < eomonth(ValidTo)
    )
    SELECT AccountNumber, MonthEndDate
    FROM cteEndMonthDates
    order by AccountNumber, MonthEndDate

Answer 3

这应该能行。

    ;WITH Span AS (

        SELECT 
            AccountNumber,
            ValidFrom AS Valid

        FROM dbo.Input
        UNION ALL
        SELECT 
            AccountNumber,
            DATEADD(DAY, 1, Span.Valid) AS Valid
        FROM Span
            WHERE DATEADD(DAY, 1, Span.Valid) <= (SELECT ValidTo FROM dbo.Input WHERE AccountNumber = Span.AccountNumber)
    )
    SELECT * FROM Span
    ORDER BY Span.AccountNumber, Span.Valid
    OPTION (MAXRECURSION 0);