July 16, 2004 at 10:18 am
How can I find out if I am missing a date?
In the following example, I am missing July 8th. FIrst record is from
June 25 to July 01 then
July 02 to July 07 then
July 09 to July 15
I want to find a data error that says that the July 07 should be July 08 and do it, if I can without looping. My hunch is to join the table to itself and use a combination of > and < in the joins.
Thanks
CREATE TABLE Test(
ID int CONSTRAINT PK_Test PRIMARY KEY,
StartDate DateTime NOT NULL,
EndDate DateTIme NOT NULL)
INSERT INTO Test(ID, StartDate, EndDate)
VALUES(1, 'Jun 25 2004', 'Jul 1 2004')
INSERT INTO Test(ID, StartDate, EndDate)
VALUES(2, 'Jul 2 2004', 'Jul 7 2004')
INSERT INTO Test(ID, StartDate, EndDate)
VALUES(3, 'Jul 9 2004', 'Jul 15 2004')
INSERT INTO Test(ID, StartDate, EndDate)
VALUES(4, 'Jul 16 2004', 'Jul 22 2004')
SELECT ID, StartDate, EndDate
FROM Test
July 16, 2004 at 12:01 pm
hey, try this to see if that is what you are looking for:
SELECT
startdate_from = t1.startdate,
enddate_from = t1.enddate,
startdate_to = t2.startdate,
enddate_to = t2.enddate,
potential_error_id = t2.ID,
error_msg =
CASE
WHEN DATEDIFF(d, t1.enddate, t2.startdate) < 0 THEN 'Error: An overlap exists in date ranges'
WHEN DATEDIFF(d, t1.enddate, t2.startdate) = 0 THEN 'Error: [enddate_from] equals [startdate_to]'
WHEN DATEDIFF(d, t1.enddate, t2.startdate) = 1 THEN 'OK, so far ...'
WHEN DATEDIFF(d, t1.enddate, t2.startdate) = 2 THEN 'Error: Date ' + CAST( DATEADD(d, -1, t2.startdate) AS VARCHAR) + ' is missing.'
WHEN DATEDIFF(d, t1.enddate, t2.startdate) > 2 THEN 'Error: Two or more dates are missing in ranges'
END
FROM
Test t1, Test t2
WHERE
t2.ID > 1 AND
t1.ID = t2.ID - 1
The query assumes that the ID column and date ranges have a linear ascending order as you originally posted.
Hope this helps ...
JP
July 16, 2004 at 12:27 pm
Thanks - This does work for what I am trying to do.
I changed the From clause to
FROM Test t1 INNER JOIN
Test t2 ON t2.ID > 1 AND t1.ID = (t2.ID - 1)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply