borenl
asked on
SQL Query for detecting Date sequence break
I have one table in SQL Server 2000 database with the following structure.
ID (Identity Column)
User ID
Amount
Measure Date (DateTime column)
The users enters one record per day per user in this table. Sometimes if user is not operational then Admin can override and enter the 0 amount data for user. Admin can also enter this data in advance for future dates. So the date column data can look like the following for a particuler user.
1/1/2008
1/2/2008
1/3/2008
1/4/2008
1/10/2008
1/11/2008
1/12/2008
1/13/2008
Now you can notice that there are no dates between 1/5 and 1/9.
My question is how can I identify the break in date sequence using sql query. In above example I want to get either 1/4 or 1/5 as sql query result.
Thanks,
ID (Identity Column)
User ID
Amount
Measure Date (DateTime column)
The users enters one record per day per user in this table. Sometimes if user is not operational then Admin can override and enter the 0 amount data for user. Admin can also enter this data in advance for future dates. So the date column data can look like the following for a particuler user.
1/1/2008
1/2/2008
1/3/2008
1/4/2008
1/10/2008
1/11/2008
1/12/2008
1/13/2008
Now you can notice that there are no dates between 1/5 and 1/9.
My question is how can I identify the break in date sequence using sql query. In above example I want to get either 1/4 or 1/5 as sql query result.
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks lot.
SELECT TOP 1 CurrentDate FROM Table1 WHERE CurrentDate < @PassedDate ORDER BY CurrentDate DESC
And then use the following select statement:
SELECT CurrentDate, dbo.PreviousDate(CurrentDa