CODEWITHSUNDEEP
sql
joe
joe

Reputation: 1473

Display dates in between dates in SQL

I have a simple table below with 5 records. 

Id:    Date
123    9/1/15
123
123
123
123    9/5/15

How do I write in SQL server to display the missing dates from 9/2/15 to 9/4/15?

I was thinking about using a loop but my skills are not there yet. Basically, I like to know how to write a SQL script that displays the missing dates.

Upvotes: 0

Views: 81

Answers (3)

Ruud Helderman
Ruud Helderman

Reputation: 11018

I am guessing you want something like this:

WITH T(ID, Date, MaxDate) AS (
    SELECT ID, MIN(Date), MAX(Date) FROM MyTable GROUP BY ID
    UNION ALL
    SELECT ID, DATEADD(day, 1, Date), MaxDate FROM T WHERE Date < MaxDate 
)
SELECT ID, Date FROM T ORDER BY ID, Date

Please follow this fiddle to see it in action: http://sqlfiddle.com/#!6/682180/2/0

Credit: my query is largely based on this answer (proposed by Rubens): Getting Dates between a range of dates

I must say that I don't like the idea of the database layer being responsible for representation of data. Please consider doing a repair on the table itself, filling in the missing dates. Though I have no idea how; there seems to be no unique identifier to tell the three NULL records apart. You may want to take this back to the source of the data; looks like you have been importing an Excel sheet. Try to repair it there.

Upvotes: 1

Nitin Tripathi
Nitin Tripathi

Reputation: 1254

try using MINUS - demonstrated below for numbers, you can use it for dates. MINUS should make the query maintainable and it will simply the query.

SQL Fiddle

Query 1:

select n from
 ( select rownum n from dual connect by level <= 5)
 where n >= 1
 MINUS
 select n from
 ( select rownum n from dual connect by level <= 2)
 where n >= 1

Results: | N | |---| | 3 | | 4 | | 5 |

Hope it will help you :)

Upvotes: 0

sadmicrowave
sadmicrowave

Reputation: 40892

What about something like this?

DECLARE @TMP TABLE ( ID INT, ADMIT DATE )

INSERT INTO @TMP ( ID, ADMIT )
VALUES   ( 123, '2015-09-01' )
        ,( 123, NULL )
        ,( 123, NULL )
        ,( 123, NULL )
        ,( 123, '2015-09-05' )

SELECT TMP.ID
      ,CASE 
          WHEN TMP.ADMIT IS NULL
             THEN DATEADD(D,(ROW_NUMBER() OVER (PARTITION BY TMP.ID, TMP.ADMIT ORDER BY TMP.ID ASC)),B.MIN_ADMIT)
          ELSE TMP.ADMIT 
       END  AS NEW_DATE
FROM @TMP AS TMP
      INNER JOIN ( SELECT ID, MIN(ADMIT) AS MIN_ADMIT
                   FROM @TMP
                   GROUP BY ID
                 ) B ON B.ID = TMP.ID
ORDER BY TMP.ID, NEW_DATE ASC

Basically, this just creates a temp table with your dummy data so we can test with some values. Then performs the query that uses a row ranking for each row number and add's that value to the minimum date found in the original list...

We need the INNER JOIN because we need to do a GROUP BY clause to get the MIN date from your original table. We can't do a GROUP BY in our original SELECT because that will begin excluding some rows as we only have ID and ADMIT to group off of; therefore the 3 records will NULL would be truncated to 1 record.

Upvotes: 0

Related Questions