查找缺失的周期，并确定之前是否连续

Question

我目前正在处理一项要求，以找到缺失周期，然后确定它在缺失周期之前是否连续

下面是我的示例表结构。

第一步：在下面的示例表中查看最近6个月（2014 7 - 2014 12）。它失踪了两个月8&11.举第一个例子8。

第二步：从第一个实例丢失返回6个月-（2014 2- 2014 7）-查看它们是否丢失了任何月份。如果否（所有内容都是连续的）-选择/包括此记录如果是（缺少几个月）-不要选择此记录。

Year   Month   
2014    1
2014    2
2014    3
2014    4
2014    5
2014    6
2014    7
2014    9
2014    10
2014    12

我可以选择最后6条记录，并按行号进行分区，看看它是否缺少任何记录。但我不知道如何找到它是否连续，并选择缺失的时期。

我正在尝试在Transact-SQL中进行尽可能多的过滤，以便能够专注于c#上的其他验证。

我创建的查询查找第一个时段丢失（未完成）

SELECT f.TYEAR,f.TMONTH, f.TMONTH+1 AS MISSING FROM #TEMPTABLE AS F
LEFT OUTER JOIN #TEMPTABLE AS F2 ON f.TMONTH+1 = f2.TMONTH
WHERE f2.TAXPERIOD IS NULL

注：以上示例可以跨越两个日历年。2013毫米-2014毫米

Answer 1

这个已经测试过了，我花了一些时间在上面，如果你对结果满意，请告诉我，如果是，请投票，干杯：-）PS：表dbo。Months_and_Years包含您的数据（缺少2个月）

            CREATE TABLE dbo.Test_Table  ( 
            [Year] SMALLINT,
            [Month] TINYINT
            );
            INSERT INTO dbo.Test_Table
            VALUES 
            (2014, 1),
            (2014, 2),
            (2014, 3),
            (2014, 4),
            (2014, 5),
            (2014, 6),
            (2014, 7),
            (2014, 9),
            (2014, 10),
            (2014, 12);


            DECLARE @MinValue TINYINT = 1
            DECLARE @MaxValue TINYINT = 100
            WHILE @MinValue < = @MaxValue
            BEGIN
            DECLARE @Missing_Month TINYINT = (
                        SELECT TOP 1 A.RowID
                        FROM (
                        SELECT DENSE_RANK() OVER ( ORDER BY [month])  AS RowID ,
                         *
                        FROM dbo.Test_Table
                        ) AS A
                        WHERE A.RowID <> A.[Month]
                        )
            SELECT  @Missing_Month
            IF @Missing_Month IS NULL
            BREAK
            ELSE
            INSERT INTO dbo.Test_Table
            VALUES (2014, @Missing_Month)
            SET @MinValue = @MinValue + 1
            END

            -- Check your results ---
            SELECT A.*
            FROM dbo.Test_Table AS A
LEFT JOIN dbo.Months_and_Years AS B  ON A.[Month] = B.[Month]
            WHERE B.[Month] IS NULL;

Answer 2

我认为下面的代码应该可以工作，尽管我还没有对它进行正确的测试。但你会想到

DECLARE @TmpBaseTable TABLE ([Year] SMALLINT, [Month] TINYINT)
DECLARE @TmpSixMonthTable TABLE (TmpYear SMALLINT, TmpMonth TINYINT)
DECLARE @TmpDate DATE
DECLARE @MissingYear SMALLINT
DECLARE @MissingMonth TINYINT
DECLARE @TmpCount TINYINT

INSERT INTO @TmpBaseTable ([Year], [Month])
SELECT 2014, 1 UNION ALL
SELECT 2014, 2 UNION ALL
SELECT 2014, 3 UNION ALL
SELECT 2014, 4 UNION ALL
SELECT 2014, 5 UNION ALL
SELECT 2014, 6 UNION ALL
SELECT 2014, 7 UNION ALL
SELECT 2014, 8 UNION ALL
SELECT 2014, 9 UNION ALL
SELECT 2014, 10 UNION ALL
SELECT 2014, 11 UNION ALL
SELECT 2014, 12 UNION ALL
SELECT 2015, 1 UNION ALL
SELECT 2015, 3 UNION ALL
SELECT 2015, 4
SELECT TOP 1 @TmpDate = CAST(CAST(tmpYear AS VARCHAR) + '-' + CAST(tmpMonth AS VARCHAR) + '-' + CAST(1 AS VARCHAR) AS DATE)
FROM
(
    SELECT ROW_NUMBER() OVER(ORDER BY [Year], [Month]) AS RowID, [Month] AS tmpMonth, [Year] AS tmpYear
    FROM @TmpBaseTable
)
tmp ORDER BY RowID DESC
INSERT INTO @TmpSixMonthTable  (TmpMonth, TmpYear)
SELECT DATEPART(MONTH, DATEADD (MONTH, -5, @TmpDate)), DATEPART(YEAR, DATEADD (MONTH, -5, @TmpDate)) UNION ALL
SELECT DATEPART(MONTH, DATEADD (MONTH, -4, @TmpDate)), DATEPART(YEAR, DATEADD (MONTH, -4, @TmpDate)) UNION ALL
SELECT DATEPART(MONTH, DATEADD (MONTH, -3, @TmpDate)), DATEPART(YEAR, DATEADD (MONTH, -3, @TmpDate)) UNION ALL
SELECT DATEPART(MONTH, DATEADD (MONTH, -2, @TmpDate)), DATEPART(YEAR, DATEADD (MONTH, -2, @TmpDate)) UNION ALL
SELECT DATEPART(MONTH, DATEADD (MONTH, -1, @TmpDate)), DATEPART(YEAR, DATEADD (MONTH, -1, @TmpDate)) UNION ALL
SELECT DATEPART(MONTH, DATEADD (MONTH, -0, @TmpDate)), DATEPART(YEAR, DATEADD (MONTH, -0, @TmpDate))
SELECT TOP 1 @MissingMonth = tmpSix.TmpMonth, @MissingYear = tmpSix.TmpYear FROM @TmpSixMonthTable tmpSix
LEFT OUTER JOIN @TmpBaseTable tmpBase ON tmpSix.TmpMonth = tmpBase.[Month] AND tmpSix.TmpYear = tmpBase.[Year]
WHERE tmpBase.[Year] IS NULL
SET @TmpDate = CAST(CAST(@MissingYear AS VARCHAR) + '-' + CAST(@MissingMonth AS VARCHAR) + '-' + CAST(1 AS VARCHAR) AS DATE)
DELETE FROM @TmpSixMonthTable
INSERT INTO @TmpSixMonthTable  (TmpMonth, TmpYear)
SELECT DATEPART(MONTH, DATEADD (MONTH, -6, @TmpDate)), DATEPART(YEAR, DATEADD (MONTH, -6, @TmpDate)) UNION ALL
SELECT DATEPART(MONTH, DATEADD (MONTH, -5, @TmpDate)), DATEPART(YEAR, DATEADD (MONTH, -5, @TmpDate)) UNION ALL
SELECT DATEPART(MONTH, DATEADD (MONTH, -4, @TmpDate)), DATEPART(YEAR, DATEADD (MONTH, -4, @TmpDate)) UNION ALL
SELECT DATEPART(MONTH, DATEADD (MONTH, -3, @TmpDate)), DATEPART(YEAR, DATEADD (MONTH, -3, @TmpDate)) UNION ALL
SELECT DATEPART(MONTH, DATEADD (MONTH, -2, @TmpDate)), DATEPART(YEAR, DATEADD (MONTH, -2, @TmpDate)) UNION ALL
SELECT DATEPART(MONTH, DATEADD (MONTH, -1, @TmpDate)), DATEPART(YEAR, DATEADD (MONTH, -1, @TmpDate))

SELECT @TmpCount = COUNT(1) FROM @TmpSixMonthTable tmp1
INNER JOIN @TmpBaseTable tmp2 ON tmp1.TmpMonth = tmp2.[Month] AND tmp1.TmpYear = tmp2.[Year]
IF(@TmpCount = 6)
BEGIN
    SELECT tmp2.[Month], tmp2.[Year] FROM @TmpSixMonthTable tmp1
    INNER JOIN @TmpBaseTable tmp2 ON tmp1.TmpMonth = tmp2.[Month] AND tmp1.TmpYear = tmp2.[Year]
END

有一种比这更聪明的方法：）