Get missing dates in table

• Trestire

  SSC Enthusiast

  Points: 129

  More actions

  August 18, 2014 at 5:27 pm

  #292470

  Hi all,

  I've a request: I've a table with a date column and an if numeric data type.

  I've to check from now on 7 days ago if any value is missing in my date fiield.

  So if I've f.i. in my table

  ID DAYS

  1 2014-08-11

  2 2014-08-12

  3 2014-08-13

  4 2014-08-14

  5 2014-08-17

  then my output should be:

  2014-08-15

  2014-08-16

  2014-08-18

  any tips on how to do this?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 18, 2014 at 5:43 pm

  #1739061

  You could create a cte with all the dates and check for the ones you're missing.

  Here's an example but you might want to change the code to include more values if necessary with a larger tally table.

  WITH Dates(d) AS(

  SELECT DATEADD( dd, DATEDIFF( dd, n, GETDATE()), 0)

  FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7))e(n)

  )

  SELECT *

  FROM Dates

  WHERE NOT EXISTS(SELECT * FROM Mytable t WHERE t.Days = Dates.d)

  Reference: http://www.sqlservercentral.com/articles/T-SQL/62867/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2