finding missing dates in a sequence

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?
jayi should have also said i do not want to have a lookup table, ie a table tha
t
permenantly stores dates.
cheers jay
jay
"jay" wrote:

> I have 200 tables that have data entered into the daily. I need to identi
fy
> 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?
> --
> jay|||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
"jay" wrote:
> i should have also said i do not want to have a lookup table, ie a table t
hat
> permenantly stores dates.
> cheers jay
> --
> jay
>
> "jay" wrote:
>|||jay <jay@.discussions.microsoft.com> wrote:
> 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?
It'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" <jay@.discussions.microsoft.com> wrote in message
news:BE8860E2-EB68-4EA0-A389-B5D53996608E@.microsoft.com...
> "jay" wrote:
>
to identify
> i should have also said i do not want to have a lookup table, ie a
table that
> permenantly stores dates.
> cheers jay
> --
> jay
>
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.|||>i should have also said i do not want to have a lookup table, ie a table
>that
> permenantly stores dates.
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
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:efmMbhe$FHA.3568@.TK2MSFTNGP09.phx.gbl...
> 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
>
>|||"Jay Walker" <jay@.bladecomputing.com.au> wrote in message
news:eCF4kyf$FHA.3064@.TK2MSFTNGP10.phx.gbl...
> 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
>
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.NOTVALID@.GETRIDOF.luminousrain.com) writes:
> What do you mean? A calendar table is loaded, and that is that.
> There is no "maintenance" (at least not in our lifetimes).
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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||jay (jay@.discussions.microsoft.com) writes:
> 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
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
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql