Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 14th, 2004, 02:23 AM
Registered User
 
Join Date: Oct 2004
Location: Cochin, kerala, India.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to hari-kumar-vadakkeveedu
Default Find out missing record

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
 
Old October 14th, 2004, 02:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
 
Old October 14th, 2004, 03:48 AM
Registered User
 
Join Date: Oct 2004
Location: Cochin, kerala, India.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to hari-kumar-vadakkeveedu
Default

Thanks for ur response,

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

-Hari
 
Old October 15th, 2004, 01:09 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
To find the missing numbers in a list of cells yogeshyl Excel VBA 2 December 13th, 2007 02:02 AM
VBScript find missing number function?? mat41 Classic ASP Professional 6 February 12th, 2007 07:07 PM
Compare two table - Find missing record lawsoncobol Access VBA 5 August 4th, 2006 01:18 AM
Compare Two MS access db file and find missing rec lawsoncobol Reporting Services 0 August 3rd, 2006 04:14 AM
Anybody find the missing code in ch6? nickolas BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 1 January 29th, 2006 10:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.