查找與日曆相比缺失的日期 (Find missing date as compare to calendar)

問題描述

查找與日曆相比缺失的日期 (Find missing date as compare to calendar)

我簡要地解釋了問題。

select distinct  DATE from #Table where   DATE >='2016‑01‑01'

輸出：

Date 
2016‑11‑23  
2016‑11‑22  
2016‑11‑21  
2016‑11‑19  
2016‑11‑18  

現在我需要找出缺失的日期，並與我們從 2016 年開始的日曆日期進行比較

這裡缺少日期“2016‑11‑20”。

我想要缺少日期的列表。

感謝您閱讀本文。祝你有美好的一天。

參考解法

方法 1:

You need to generate dates and you have to find missing ones. Below with recursive cte i have done it

  ;WITH CTE AS
    (
    SELECT CONVERT(DATE,'2016‑01‑01') AS DATE1
    UNION ALL
    SELECT DATEADD(DD,1,DATE1) FROM CTE WHERE DATE1<'2016‑12‑31'
    )
    SELECT DATE1 MISSING_ONE FROM CTE
    EXCEPT 
    SELECT * FROM #TABLE1
    option(maxrecursion 0)

方法 2:

You need to generate the dates and then find the missing ones. A recursive CTE is one way to generate a handful of dates. Another way is to use master..spt_values as a list of numbers:

with n as (
      select row_number() over (order by (select null)) ‑ 1 as n
      from master..spt_values
     ),
     d as (
      select dateadd(day, n.n, cast('2016‑01‑01' as date)) as dte
      from n
      where n <= 365
     )
select d.date
from d left join
     #table t
     on d.dte = t.date
where t.date is null;

If you are happy enough with ranges of missing dates, you don't need a list of dates at all:

select date, (datediff(day, date, next_date) ‑ 1) as num_missing
from (select t.*, lead(t.date) over (order by t.date) as next_date
      from #table t
      where t.date >= '2016‑01‑01'
     ) t
where next_date <> dateadd(day, 1, date);

方法 3:

Using CTE and get all dates in CTE table then compare with your table.

CREATE TABLE #yourTable(_Values DATE)
INSERT INTO #yourTable(_Values)
SELECT '2016‑11‑23' UNION ALL 
SELECT '2016‑11‑22' UNION ALL  
SELECT '2016‑11‑21' UNION ALL
SELECT '2016‑11‑19' UNION ALL  
SELECT '2016‑11‑18'   


DECLARE @DATE DATE = '2016‑11‑01'
;WITH CTEYear (_Date) AS
(
  SELECT @DATE
  UNION ALL
  SELECT DATEADD(DAY,1,_Date)
  FROM CTEYear
  WHERE _Date < EOMONTH(@DATE,0)
)

SELECT * FROM CTEYear
WHERE NOT EXISTS(SELECT 1 FROM #yourTable WHERE _Date = _Values)
OPTION(maxrecursion 0)

(by Mr. Bhosale、Tharunkumar Reddy、Gordon Linoff、Mansoor)

參考文件

1. Find missing date as compare to calendar (CC BY‑SA 2.5/3.0/4.0)