I have 200 tables that have data entered into the daily.  I need to identify
all dates that are missing from the tables.

for example:
date       column1        column2
1/1/2005     5                 20
2/1/2005     67               35
4/1/2005      3                17
5/1/2005      9                 6
8/1/2005      7                99


I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing

can you please assist?

i should have also said i do not want to have a lookup table, ie a table that
permenantly stores dates. 
cheers jay

Show quoteHide quote

How about a temporary lookup table?

DROP TABLE #tmp_data
CREATE TABLE #tmp_data ( entry_date DATETIME PRIMARY KEY, col1 INT NOT NULL,
col2 INT NOT NULL )

SET NOCOUNT ON

INSERT INTO #tmp_data VALUES ( '20050101', 5, 20 )
INSERT INTO #tmp_data VALUES ( '20050102', 67, 35 )
INSERT INTO #tmp_data VALUES ( '20050104', 3, 17 )
INSERT INTO #tmp_data VALUES ( '20050105', 9, 6 )
INSERT INTO #tmp_data VALUES ( '20050108', 7, 99 )


DROP TABLE #tmp_lookup
CREATE TABLE #tmp_lookup ( entry_date DATETIME PRIMARY KEY )

DECLARE @i            INT
DECLARE @stop        INT
DECLARE @min_date    DATETIME
DECLARE @max_date    DATETIME

-- Initialise
SET @i = 0

-- Calculate the range of dates to be added to the temp lookup table
SELECT
    @min_date = MIN( entry_date ),
    @max_date = MAX( entry_date )
FROM #tmp_data

SET @stop = DATEDIFF( day, @min_date, @max_date ) + 1

-- Add the dates to the lookup table
WHILE @i < @stop
BEGIN
    INSERT INTO #tmp_lookup SELECT DATEADD( day, @i, @min_date )
    SET @i = @i + 1
END

SET NOCOUNT OFF

-- Show the missing values
SELECT t1.*
FROM #tmp_lookup t1
    LEFT JOIN #tmp_data t2 ON t1.entry_date = t2.entry_date
WHERE t2.entry_date IS NULL

This could even be wrapped in a parameterized stored procedure.

Let me know how you get on.


Damien

Show quoteHide quote

Show quote Hide quote jay,

Why?

Books on SQL and RDBMs recommend it.  Many top names on the subjects
recommend it.  It's the way to go.


Sincerely,

Chris O.

WHY NOT?  Do you also tell the doctor, protect me from the flu, but don't
bring any of that flu vaccine near me!

A calendar table seems to be exactly what you need, and is going to be far
more efficient than looping solutions or generating your entire date range
on the fly every time.  Please read http://www.aspfaq.com/2519

because...the calendar table would need to be kept up to date...who would do
that?  you do not know the restrictions on the situation so please do not so
easily pass judgement when you do not know all the issues!

I appreciate any help NOT judgements from ill informed people!

cheers jay

Show quoteHide quote

date...who would do
do not so
Jay Walker,

Aaron is among the best informed people around here.

Also, you did not explain the restrictions of your situation.  I
asked, earlier, what those restrictions were, and still have no
answer.

You mentioned: "because...the calendar table would need to be kept
up to date...who would do  that?"

What do you mean?  A calendar table is loaded, and that is that.
There is no "maintenance" (at least not in our lifetimes).


Sincerely,

Chris O.

Chris2 (rainofsteel.NOTVA***@GETRIDOF.luminousrain.com) writes:
Depends on what you fill it with. If you fill it with dates, and only
dates, you can fill it up until 2150 or so. And for Jay's problem this
would do.

But for a more elaborate calendar that keeps track of business days,
there is of course maintenance to do, as holidays are changed. For instance,
my pocket calendar for 2005 printed May 16th as red, and June 6th as
black, when the days come, May 16th was a busiess day and June 6th was not.

Show quote Hide quote Erland,

The OP entitled this thread "finding missing dates in a sequence"
(not "finding missing dates in a business calendar").  That only
requires a table of sequential ascending dates.  The example
provided suggests this, as well.

Even in a business calendar table, if human maintenance is required,
this means that there are non-predictable elements in the calendar.
If this is so, no possible TSQL solution would work, either.  If you
can't predict and calculate something into a calendar table in
advance, then any TSQL solution will also not be able to calculate
it (yes, the statement appears partially redundant, as TSQL would be
building the calendar table, too; but I'm sure you realize what I
mean).  The TSQL would have to be "maintained" in the same way that
the calendar table would have to be "maintained", only it would
require a developer making code changes to the database (with all
the testing that it implies), instead of an end-user looking at a
calendar control on a GUI making adjustments to the calendar table
that way.


Sincerely,

Chris O.

jay <j**@discussions.microsoft.com> wrote:
Show quoteHide quoteIt's difficult to generate data that isn't there with an SQL query. Your
best bet is to write a stored procedure that does the job.

Another approach is to find wholes in the data sequence. Not exactly what
you want but this might work (untested):

select t1.ts, t2.mts
from tab t1, (
  select min(ts) as mts
  from tab tx
  where tx.ts > t1.ts
) t2
where datediff('dd', t1.ts, t2.mts) > 1
order by t1.ts

Kind regards

    robert

jay (j**@discussions.microsoft.com) writes:
Here is a query that does not use a calendar table. It will not list all
dates though, only the first and last date in an interval. Also, performance
is not likely to be fantastic. To that end a calendar table will be
better. For this reason, I'm including a script that fills up a dates
table with all dates from 1990 to 2149.

Here is the query (runs in Northwind):

   SELECT gapstart, MIN(gapend)
   FROM   (select gapstart = dateadd(DAY, 1, A.OrderDate)
           FROM   Orders A
           WHERE  NOT EXISTS
                  (SELECT *
                   FROM   Orders B
                   WHERE  B.OrderDate = dateadd(DAY, 1, A.OrderDate))) X
   JOIN   (select gapend = dateadd(DAY, -1, A.OrderDate)
           FROM   Orders A
           WHERE  NOT EXISTS
                  (SELECT *
                   FROM   Orders B
                   WHERE  B.OrderDate = dateadd(DAY, -1, A.OrderDate))) Y
       ON gapstart <= gapend
   GROUP BY gapstart
   ORDER BY gapstart


And here is the script:

   CREATE TABLE dates (
      thedate   aba_date  NOT NULL,
      CONSTRAINT pk_dates PRIMARY KEY (thedate)
   )


   -- Make sure it's empty.
   TRUNCATE TABLE dates
   go
   -- Get a temptable with numbers. This is a cheap, but not 100% reliable.
   -- Whence the query hint and all the checks.
   SELECT TOP 80001 n = IDENTITY(int, 0, 1)
   INTO   #numbers
   FROM   sysobjects o1
   CROSS  JOIN sysobjects o2
   CROSS  JOIN  sysobjects o3
   CROSS  JOIN  sysobjects o4
   OPTION (MAXDOP 1)
   go
   -- Make sure we have unique numbers.
   CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
   go
   -- Verify that table does not have gaps.
   IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
      (SELECT MIN(n) FROM #numbers) = 0 AND
      (SELECT MAX(n) FROM #numbers) = 80000
   BEGIN
      DECLARE @msg varchar(255)

      -- Insert the dates:
      INSERT dates (thedate)
         SELECT dateadd(DAY, n, '19800101')
         FROM   #numbers
         WHERE  dateadd(DAY, n, '19800101') < '21500101'

      SELECT @msg = 'Inserted ' + ltrim(str(@@rowcount)) +
                    ' rows into #numbers'
      PRINT @msg
   END
   ELSE
      RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
   go
   DROP TABLE #numbers

Customer specific price matrix design (Long)
Update Query question.
The command(s) completed successfully. - Can i turn this output off?
preparing monthly report
Parsing spaces