none
missing dates in column RRS feed

  • Question

  • Hi,
    How is it possible to identify the missing dates inside a date column of a table?

    Two questions please?
    1-
    For example, table1 has a date column which has DAILY dates from last year to now, then how can I identify the missing dates

    2-
    For example, table2 has a date column which has Monthly dates from last year to now, then how can I identify the missing dates

    Thank you

    Thursday, September 12, 2019 2:34 PM

Answers

All replies

  • You need to have a calendar table  and compare them 

    with tmp(plant_date) as
    (
       select cast('20090101' as datetime)
       union all
       select plant_date + 1
         from tmp
        where plant_date < '20091231'
    )

    select* into calendar
      from  tmp
    option (maxrecursion 0)

    select * from your table where not exists (select * from calendar where calendar.plant_date=yourtable.dt)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Yitzhak Khabinsky Thursday, September 12, 2019 2:44 PM
    • Marked as answer by arkiboys Friday, September 13, 2019 6:21 AM
    Thursday, September 12, 2019 2:38 PM
    Answerer
  • Hi

    Please find the below script which would fulfill the above need.

    Hope this is helpful!

    Create table #test (a int) 
    Declare @a int=1,@init int = 367;
    WHILE (@a < @init)
    BEGIN
    	INSERT INTO #test VALUES (@a);
    	SET @a = @a+1;
    END
    
    DECLARE @Date date= GETDATE(), @lastDate date = DATEADD(Year,-1,GETDATE());
    
    WITH DayCalender AS(
    SELECT DATEADD(Day,a,@lastDate) DateRange
    FROM #test)
    
    SELECT DISTINCT Cal.DateRange
    FROM DayCalender Cal
    LEFT JOIN table tbl
    ON Cal.DateRange = tbl.DateColumn WHERE tbl.DateColumn is NULL

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Thursday, September 12, 2019 3:13 PM
  • In the future, would you please follow forum netiquette and post DDL? Here's my guess at what you might have posted.

    CREATE TABLE Foobard
    (foo_id CHAR(10) NOT NULL PRIMARY KEY,
     foo_date DATE NOT NULL,
    ..); 

    You should also have a calendar table. Please take the time to download the free PDF book from the University of Arizona on temporal queries in SQL by Rick Snodgrass.

    SELECT X.missing_date
       FROM (SELECT cal_date FROM Calendar
                    EXCEPT 
                   SELECT foo_date FROM Foobars)  AS X(missing_date);

    I'm now assuming that you don't need to put a range of dates on the calendar table. I also do not know what a "monthly date"  means. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, September 12, 2019 4:53 PM
  • Hi arkiboys,

    Both tables can use below code, here we go: 

    IF OBJECT_ID('table1') IS NOT NULL drop table  table1
    create table table1 ( val date)
    go
    insert into table1 values
    ('2018-05-06'),('2018-08-10'),('2019-05-15')
    
    
    DECLARE @Date1 DATE, @Date2 DATE
    SET @Date1 = '20180101'
    SET @Date2 = getdate()
    
    select * from (
    /* the calender */
    SELECT DATEADD(DAY,number,@Date1) [Date]
    FROM master..spt_values
    WHERE type = 'P'
    AND DATEADD(DAY,number,@Date1) <= @Date2)a
    where [date] not in(select val from table1)

    Further problems, feel free to let us know.

    Sabrina 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 13, 2019 2:58 AM
  • You need to have a calendar table  and compare them 

    with tmp(plant_date) as
    (
       select cast('20090101' as datetime)
       union all
       select plant_date + 1
         from tmp
        where plant_date < '20091231'
    )

    select* into calendar
      from  tmp
    option (maxrecursion 0)

    select * from your table where not exists (select * from calendar where calendar.plant_date=yourtable.dt)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thank you
    Friday, September 13, 2019 6:21 AM