Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1148
  • Last Modified:

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,

0
borenl
Asked:
borenl
1 Solution
 
GHCS_MarkCommented:
You could have a function called PreviousDate() that returns
SELECT TOP 1 CurrentDate FROM Table1 WHERE CurrentDate < @PassedDate ORDER BY CurrentDate DESC

And then use the following select statement:

SELECT CurrentDate, dbo.PreviousDate(CurrentDate) as PreviousDate, DATEDIFF(D,CurrentDate,dbo.PreviousDate(CurrentDate)) AS DaysDifference
0
 
ishandoCommented:
How about

select MeasureDate
from table1 t1
where exists (select 1 from table1 t2 where t2.UserId = t1.UserId and t2.MeasureDate > t1.MeasureDate)
and not exists (select 1 from table1 t3 where t3.UserId = t1.UserId and t3.MeasureDate = t1.MeasureDate+1)
0
 
borenlAuthor Commented:
Thanks lot.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now