none
how to find missing dates from exting table RRS feed

  • Question

  • I have one table with signin date colum and all other colums.I need to find out missing dates from the singin date colum and all columns.
    Kindly help me  on this.
    Thursday, June 18, 2009 1:14 PM

Answers

  • You can use the EXCEPT operator to compare a set of continuous dates to the dates with gaps.

    USE AdventureWorks2008; 
    
    DECLARE  @MaxDate DATE, 
             @MinDate DATE, 
             @iDate  DATE 
    
    -- SQL Server table variable 
    DECLARE  @DateSequence TABLE( 
                              DATE DATE 
                              ) 
    
    SELECT @MaxDate = Convert(DATE,Max(orderdate)), 
           @MinDate = Convert(DATE,Min(orderdate)) 
    FROM   sales.salesorderheader 
    
    SET @iDate = @MinDate 
    
    WHILE (@iDate <= @MaxDate) 
      BEGIN 
        INSERT @DateSequence
        SELECT @iDate 
         
        SET @iDate = Convert(DATE,Dateadd(DAY,1,@iDate)) 
      END 
    
    SELECT Gaps = DATE 
    FROM   @DateSequence
    EXCEPT 
    SELECT DISTINCT Convert(DATE,orderdate) 
    FROM   Sales.SalesOrderHeader
    GO 
    /* Results
    
    Gaps
    2001-08-16
    2001-09-11
    2001-10-24
    */

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Thursday, June 18, 2009 3:41 PM
    Moderator
  • Please, provide table structure, sample data in the form of insert statements, and expected result.

    What do you mean by "missing dates and all columns"?


    Example:

    DECLARE @t TABLE (
    dt DATETIME NOT NULL UNIQUE CHECK (dt = DATEADD([day], DATEDIFF([day], '19000101', dt), '19000101'))
    )
    
    DECLARE @dt DATETIME
    
    SET @dt = DATEADD([day], DATEDIFF([day], '19000101', GETDATE()), '19000101');
    
    INSERT INTO @t VALUES(@dt - 10);
    INSERT INTO @t VALUES(@dt - 9);
    INSERT INTO @t VALUES(@dt - 5);
    INSERT INTO @t VALUES(@dt - 4);
    INSERT INTO @t VALUES(@dt - 3);
    INSERT INTO @t VALUES(@dt - 0);
    
    SELECT *
    FROM @t
    ORDER BY dt;
    
    SELECT
    	a.dt + 1 AS rs,
    	(
    	SELECT MIN(d.dt)
    	FROM @t AS d
    	WHERE d.dt > a.dt
    	) - 1 AS re
    FROM
    	@t AS a
    WHERE
    	NOT EXISTS (
    	SELECT *
    	FROM @t AS b
    	WHERE b.dt = a.dt + 1
    	)
    	AND a.dt < (SELECT MAX(c.dt) FROM @t AS c);
    GO

     

    You can find other methods to find gaps in a sequence and full comparison among them, in Itzik's last book Inside Microsoft® SQL Server® 2008: T-SQL Querying.


    AMB

    Thursday, June 18, 2009 1:42 PM
    Moderator
  • I think the easiest method here and probably the most performant is to use a calendar table or a numbers table.  You can join directly left join from the calendar/ numbers table. Any nulls represent a gap.

    Calendar TAble: http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
    Numbers TAble: http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

    Here are some samples:

    Note: I recommend that if you do plan to use variables, you use the logic within a stored procedure or inline table valued function, so you can paramertize the query. If not, you cannot gaurentee that the optimizer will know the cardinality of the predicate, which may lead to a sub-optimal plan.

    DECLARE @t TABLE (
    dt DATETIME PRIMARY KEY CLUSTERED 
    )
    
    INSERT INTO @t VALUES(GETDATE() - 10);
    INSERT INTO @t VALUES(GETDATE() - 9);
    INSERT INTO @t VALUES(GETDATE() - 5);
    INSERT INTO @t VALUES(GETDATE() - 4);
    INSERT INTO @t VALUES(GETDATE() - 3);
    INSERT INTO @t VALUES(GETDATE() - 0);
    
    DECLARE @Start_Dt DATETIME,
    		@End_Dt DATETIME
    		
    SET @Start_Dt = '2009-06-01'
    SET @End_Dt = '2009-07-01'
    
    SELECT 
    	c.dte
    FROM Calendar c
    LEFT JOIN @t t
    	ON CONVERT(SMALLDATETIME,CONVERT(CHAR(10),t.dt,101)) = c.dte
    WHERE
    	c.dte >= @Start_Dt
    	AND  c.dte < @End_Dt
    	AND t.dt IS NULL
    
    SELECT 
    	DATEADD(DAY,n-1,0) AS Dt
    FROM tempdb..Numbers n
    LEFT JOIN @t t
    	ON n.n = CONVERT(INT,t.dt)
    WHERE 
    	n.N >= CONVERT(INT,@Start_Dt)
    	AND n.N < CONVERT(INT,@End_Dt)
    	AND t.dt IS NULL
    



    Here is the code I use to create a numbers table (Jeff Moden's code)
    --=============================================================================
    --      Setup
    --=============================================================================
    USE [tempdb]
    GO
    
    SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed
    
    DECLARE @StartTime DATETIME    --Timer to measure total duration
        SET @StartTime = GETDATE() --Start the timer
    
    --=============================================================================
    --      Create and populate a Numbers table
    --=============================================================================
    --===== Conditionally drop 
         IF OBJECT_ID('dbo.Numbers') IS NOT NULL 
            DROP TABLE dbo.Numbers
    
    --===== Create and populate the Numbers table on the fly
     SELECT TOP 45000 --equates to more than 30 years of dates
            IDENTITY(INT,1,1) AS N
       INTO dbo.Numbers
       FROM Master.dbo.SysColumns sc1,
            Master.dbo.SysColumns sc2
    
    --===== Add a Primary Key to maximize performance
      ALTER TABLE dbo.Numbers
        ADD CONSTRAINT PK_Numbers_N 
            PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
    
    --===== Let the public use it
      GRANT SELECT, REFERENCES ON dbo.Numbers TO PUBLIC
    
    --===== Display the total duration
     SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds duration'
    

    http://jahaines.blogspot.com/
    Thursday, June 18, 2009 7:56 PM
    Moderator

All replies

  • Please check the following link http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/48597e5a-f26e-4437-b752-359e73d02e62

    This issue has been talked and a few sample queries were suggested.

    If any of them is not solving the problem, you can share the reason here and we can continue for new solutions.


    Eralper
    SQL Tips & Tricks with Sample T-SQL Codes


    http://www.kodyaz.com http://www.eralper.com
    Thursday, June 18, 2009 1:32 PM
    Moderator
  • This one seems easier than that thread. And, more practice for me. :)

    WITH
        Signin(Date)
    AS
        (
         SELECT '2009-03-01 00:00:00.000' UNION ALL
         SELECT '2009-04-01 00:00:00.000' UNION ALL
         SELECT '2009-05-09 00:00:00.000'
        ),
        CTE(Start, Finish)
    AS
        (
         SELECT
                DATEDIFF(day, 0, MIN(Date)),
                DATEDIFF(day, 0, MAX(Date))
         FROM
                Signin
         UNION ALL
         SELECT
                Start + 1,
                Finish
         FROM
                CTE
         WHERE
                Start + 1 < Finish
        )
    SELECT
            DATEADD(day, Start, 0)
    FROM
            CTE
    WHERE
            NOT EXISTS
            (
             SELECT
                    *
             FROM
                    Signin
             WHERE
                    Signin.Date = DATEADD(day, CTE.Start, 0)
            );
    
    • Proposed as answer by R L W Monday, December 13, 2010 9:23 PM
    Thursday, June 18, 2009 1:37 PM
    Moderator
  • Please, provide table structure, sample data in the form of insert statements, and expected result.

    What do you mean by "missing dates and all columns"?


    Example:

    DECLARE @t TABLE (
    dt DATETIME NOT NULL UNIQUE CHECK (dt = DATEADD([day], DATEDIFF([day], '19000101', dt), '19000101'))
    )
    
    DECLARE @dt DATETIME
    
    SET @dt = DATEADD([day], DATEDIFF([day], '19000101', GETDATE()), '19000101');
    
    INSERT INTO @t VALUES(@dt - 10);
    INSERT INTO @t VALUES(@dt - 9);
    INSERT INTO @t VALUES(@dt - 5);
    INSERT INTO @t VALUES(@dt - 4);
    INSERT INTO @t VALUES(@dt - 3);
    INSERT INTO @t VALUES(@dt - 0);
    
    SELECT *
    FROM @t
    ORDER BY dt;
    
    SELECT
    	a.dt + 1 AS rs,
    	(
    	SELECT MIN(d.dt)
    	FROM @t AS d
    	WHERE d.dt > a.dt
    	) - 1 AS re
    FROM
    	@t AS a
    WHERE
    	NOT EXISTS (
    	SELECT *
    	FROM @t AS b
    	WHERE b.dt = a.dt + 1
    	)
    	AND a.dt < (SELECT MAX(c.dt) FROM @t AS c);
    GO

     

    You can find other methods to find gaps in a sequence and full comparison among them, in Itzik's last book Inside Microsoft® SQL Server® 2008: T-SQL Querying.


    AMB

    Thursday, June 18, 2009 1:42 PM
    Moderator
  • You can use the EXCEPT operator to compare a set of continuous dates to the dates with gaps.

    USE AdventureWorks2008; 
    
    DECLARE  @MaxDate DATE, 
             @MinDate DATE, 
             @iDate  DATE 
    
    -- SQL Server table variable 
    DECLARE  @DateSequence TABLE( 
                              DATE DATE 
                              ) 
    
    SELECT @MaxDate = Convert(DATE,Max(orderdate)), 
           @MinDate = Convert(DATE,Min(orderdate)) 
    FROM   sales.salesorderheader 
    
    SET @iDate = @MinDate 
    
    WHILE (@iDate <= @MaxDate) 
      BEGIN 
        INSERT @DateSequence
        SELECT @iDate 
         
        SET @iDate = Convert(DATE,Dateadd(DAY,1,@iDate)) 
      END 
    
    SELECT Gaps = DATE 
    FROM   @DateSequence
    EXCEPT 
    SELECT DISTINCT Convert(DATE,orderdate) 
    FROM   Sales.SalesOrderHeader
    GO 
    /* Results
    
    Gaps
    2001-08-16
    2001-09-11
    2001-10-24
    */

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Thursday, June 18, 2009 3:41 PM
    Moderator
  • I think the easiest method here and probably the most performant is to use a calendar table or a numbers table.  You can join directly left join from the calendar/ numbers table. Any nulls represent a gap.

    Calendar TAble: http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
    Numbers TAble: http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

    Here are some samples:

    Note: I recommend that if you do plan to use variables, you use the logic within a stored procedure or inline table valued function, so you can paramertize the query. If not, you cannot gaurentee that the optimizer will know the cardinality of the predicate, which may lead to a sub-optimal plan.

    DECLARE @t TABLE (
    dt DATETIME PRIMARY KEY CLUSTERED 
    )
    
    INSERT INTO @t VALUES(GETDATE() - 10);
    INSERT INTO @t VALUES(GETDATE() - 9);
    INSERT INTO @t VALUES(GETDATE() - 5);
    INSERT INTO @t VALUES(GETDATE() - 4);
    INSERT INTO @t VALUES(GETDATE() - 3);
    INSERT INTO @t VALUES(GETDATE() - 0);
    
    DECLARE @Start_Dt DATETIME,
    		@End_Dt DATETIME
    		
    SET @Start_Dt = '2009-06-01'
    SET @End_Dt = '2009-07-01'
    
    SELECT 
    	c.dte
    FROM Calendar c
    LEFT JOIN @t t
    	ON CONVERT(SMALLDATETIME,CONVERT(CHAR(10),t.dt,101)) = c.dte
    WHERE
    	c.dte >= @Start_Dt
    	AND  c.dte < @End_Dt
    	AND t.dt IS NULL
    
    SELECT 
    	DATEADD(DAY,n-1,0) AS Dt
    FROM tempdb..Numbers n
    LEFT JOIN @t t
    	ON n.n = CONVERT(INT,t.dt)
    WHERE 
    	n.N >= CONVERT(INT,@Start_Dt)
    	AND n.N < CONVERT(INT,@End_Dt)
    	AND t.dt IS NULL
    



    Here is the code I use to create a numbers table (Jeff Moden's code)
    --=============================================================================
    --      Setup
    --=============================================================================
    USE [tempdb]
    GO
    
    SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed
    
    DECLARE @StartTime DATETIME    --Timer to measure total duration
        SET @StartTime = GETDATE() --Start the timer
    
    --=============================================================================
    --      Create and populate a Numbers table
    --=============================================================================
    --===== Conditionally drop 
         IF OBJECT_ID('dbo.Numbers') IS NOT NULL 
            DROP TABLE dbo.Numbers
    
    --===== Create and populate the Numbers table on the fly
     SELECT TOP 45000 --equates to more than 30 years of dates
            IDENTITY(INT,1,1) AS N
       INTO dbo.Numbers
       FROM Master.dbo.SysColumns sc1,
            Master.dbo.SysColumns sc2
    
    --===== Add a Primary Key to maximize performance
      ALTER TABLE dbo.Numbers
        ADD CONSTRAINT PK_Numbers_N 
            PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
    
    --===== Let the public use it
      GRANT SELECT, REFERENCES ON dbo.Numbers TO PUBLIC
    
    --===== Display the total duration
     SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds duration'
    

    http://jahaines.blogspot.com/
    Thursday, June 18, 2009 7:56 PM
    Moderator