Missing Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Missing Records

Author

Topic

GJackson
Starting Member

1 Post

Posted - 2005-08-08 : 17:01:22

Hi,
I have one simple table.
I need to query two days worth of data and return records that were present yesterday but are not present today. Can someone please point me in the right direction?

Record layout:
EMAIL_1 SYS 08/07/2005 5:09:00 AM
EMAIL_1 SYS 08/08/2005 1:17:00 PM

Select statement:
SELECT SourceMachine, VolumeName, DateTimeStamp
FROM StorageReportsOpenSystems
WHERE (DateTimeStamp >= CONVERT(DATETIME, '2005-08-07 01:00:00', 102))
ORDER BY SourceMachine

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-08 : 17:14:57

You didn't post your table structure, but here goes based on my interpetation of your columns from the query...


SELECT T.SourceMachine, T.VolumeName, T.DateTimeStamp
  FROM StorageReportsOpenSystems T -- T is short for Today
  LEFT OUTER JOIN StorageReportsOpenSystems Y -- Y is for Yesterday 
    ON T.SourceMachine = Y.SourceMachine
    AND T.VolumeName = Y.VolumeName
    AND DATEDIFF(dd, Y.DateTimeStamp, T.DateTimeStamp) = 1

  WHERE Y.VolumeName IS NULL
  ORDER BY SourceMachine

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-09 : 01:35:50

See if this hepls you
http://mindsdoor.net/SQLTsql/FindGapsInSequence.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources