SQL Server查询识别特定日期丢失数据(SQL Server Query that ident

我有一个包含一个需要全天定期检查，以确保它被正确地更新数据的表。

每一天，此表应插入像这样的数据：

           CORRECT
|  Date  |  Unit  |  Paid  |
| 1/1/12 | Unit A |  YES   |
| 1/1/12 | Unit B |  YES   |
| 1/2/12 | Unit A |  NO    |
| 1/2/12 | Unit B |  NO    |

所以应该每天为单元A和单元B然而，有时我的程序返回不正确的数据和我结束了一段日期丢失数据或日期接收数据，但只有一个单元中的数据：

          INCORRECT
|  Date  |  Unit  |  Paid  |
| 1/1/12 | Unit A |  YES   |
| 1/3/12 | Unit A |  NO    |
| 1/3/12 | Unit B |  NO    |

数据丢失12年1月1日为单位B.数据丢失12年1月2日为单元A和B.

我怎么能编写一个查询将返回缺少数据和哪个单位它丢失数据的日期？

Answer 1:

您可以使用理货表生成的所有组合Date和Unit 。 当你得到所有的组合，您可以使用NOT EXISTS来获取丢失的数据。

SQL小提琴

DECLARE @minDate AS DATE
DECLARE @maxDate AS DATE

SELECT
    @minDate = MIN([Date]),
    @maxDate = MAX([Date])
FROM TestData


;WITH E1(N) AS(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
Tally(N) AS(
    SELECT TOP(DATEDIFF(DAY, @minDate, @maxDate) + 1)
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
),
CteDatesAndUnits([Date], Unit) AS(
    SELECT
        DATEADD(DAY, t.N - 1, @minDate),
        u.Unit
    FROM Tally t
    CROSS JOIN (
        SELECT DISTINCT Unit FROM TestData
    )u
)
SELECT *
FROM CteDatesAndUnits c
WHERE NOT EXISTS(
    SELECT * 
    FROM TestData
    WHERE
        [Date] = c.[Date]
        AND Unit = c.Unit
)

结果

|       Date |   Unit |
|------------|--------|
| 2012-01-01 | Unit B |
| 2012-01-02 | Unit A |
| 2012-01-02 | Unit B |

---

由于该Unit将永远是Unit A和Unit B ，您可以替换这行：

SELECT DISTINCT Unit FROM TestData

有了这个：

SELECT 'Unit A' AS Unit UNION ALL SELECT 'Unit B'

Answer 2:

那么，在这种情况下，我可能会做这样的事情：

DECLARE @FromDate date, @ToDate date 

-- Or you can use Max and Min date from your table
SELECT @FromDate = '2015-01-01',
       @ToDate = '2015-01-07'

-- use a recursive cte to get all the dates between from date and to date
;With CTE AS (
  SELECT @FromDate As TheDate
  UNION ALL
  SELECT DateAdd(Day, 1, TheDate)
  FROM CTE
  WHERE TheDate < @ToDate
)

-- select dates where all the units are missing
SELECT TheDate, 'All' as Missing_Units
FROM CTE 
LEFT JOIN YourTable ON(YourTable.Date = CTE.TheDate)
WHERE Unit IS NULL

UNION ALL

-- select dates where 'Unit B' is missing
SELECT T1.Date, 'Unit B' as Missing_Units
FROM YourTable T1 
LEFT JOIN (
    SELECT Date, Unit
    FROM YourTable
    WHERE Unit = 'Unit B'
 ) T2 ON(T1.Date = T2.Date)
WHERE T2.Unit IS NULL

UNION ALL

-- select dates where 'Unit A' is missing
SELECT T1.Date, 'Unit A' as Missing_Units
FROM YourTable T1 
LEFT JOIN (
    SELECT Date, Unit
    FROM YourTable
    WHERE Unit = 'Unit A'
 ) T2 ON(T1.Date = T2.Date)
WHERE T2.Unit IS NULL

见这里捣鼓

文章来源: SQL Server Query that identifies missing data for specific dates