Friends,:)

I have a table contains daily log information. I need to find out if any rows missing out in a given date range.

Is it possible to find out in a single select statement? or What is the near best solution?

Scenario:

Date UsrId
----- ------
10/Oct/2004 3
12/Oct/2004 4
13/Oct/2004 3
14/Oct/2004 3

My query should return false, because row for 11/Oct/2004 is missing. My processing period is 10 to 14 October 2004.

Thanks in Advance
-Hari

Do you have one record per day? If so, you could use datediff on the processing period to see how many days you should have. Then compare that with a SELECT COUNT of records between your given dates. If the two answers are the same then there are no missing records.

Thanks for ur response,

Ok, But there can be multiples records for the same day.
:)

-Hari

I found a in my archives I do not know who wrote it so therefore I can not give them the credit for it.

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

Jaime E. Maccou
Applications Analyst