Finding Non Consecutive Dates in a Query

[KB]

Not sure how to do this in SQL Server

People log in and it is recorded in a database, only one record per day. Later logins aren't important. I need to find the first day that they failed to login.


PersonID LoggedInDay
------------------------------------
1....................7/1/2009
1....................7/2/2009
1.....................7/4/2009


I need to write a query that will find that Person1 Failed to login 7/3/2009

Any ideas?

 

[KLin]

Do people log in on the weekends?

 

[KIAman]

The easiest solution is to modify the table to include a field called "Result" or "Status" that flags a user logged in or not and record all login attempts. Then you'd have a simple select statement for the failed login.

PersonID LoggedInDay Result
-----------------------------------
1............7/1/2009.......Success
1............7/2/2009.......Success
1............7/3/2009.......Failed
1............7/4/2009.......Success

select loggedinday from TABLE where personID = 1 and result = 'failed'

There are lots of holes in the current table. There is nothing to differentiate a failed login from not-attempted login. Maybe that person took the day off?

 

[WannaFly]

KIA: I don't think that will work, it sounds like the person is SKIPPING a day, not failing.

OP: There'd proobably a better way, but I've done somthing simliar by filling a table with all valid values (1/1/2009 - 12/31/2009 each day, for say the next 10 years) and just performing a NOT IN where clause. I was using int's so i'm not sure how efficient it will be with dates.

 

[ahurtt]

How do you select via a query that which does not exist? If there's no record in the table for a given day because the given person did not log in that day then what would you be selecting?

 

[BoberFett]

Outer joins are your friend to find missing data.

select PersonID, a.LoginDate
from LoginTable a left join LoginTable b on a.CustomerID = b.CustomerID and a.LoginDate + 1 = b.LoginDate
where b.LoginDate is null

Edit: Oops, had the date add in the wrong place. a.LoginDate will be the day that the person did login and b.LoginDate will be the next day if they person did login. If they didn't, it will be null.

 

[KIAman]

Originally posted by: WannaFly
KIA: I don't think that will work, it sounds like the person is SKIPPING a day, not failing.

Blah, in that case, write a job that triggers when the day rolls over to check if a login was made, if not, write a row and still update the new 'status' field as not logged in.

It just boggles me why one would utilize a database to find missing data.

Originally posted by: BoberFett
Outer joins are your friend to find missing data.

select PersonID, a.LoginDate
from LoginTable a left join LoginTable b on a.CustomerID = b.CustomerID and a.LoginDate = b.LoginDate + 1
where b.LoginDate is null

Close. This will select existing dates because of a.LoginDate. You'll have to account for a.LoginDate = b.LoginDate + 1.

select PersonID, a.LoginDate - 1
from LoginTable a left join LoginTable b on a.CustomerID = b.CustomerID and a.LoginDate = b.LoginDate + 1
where b.LoginDate is null

But even this won't account for multiple sequential day gap because it will only return the last missing day of a range of missing days.

If nothing else can change, the best way is looping through the table and check each row for logindate = null and flagging the first one found. And if that is all to the table, no row ids or keys, you'd have to use cursor to iterate through all rows by date. Very messy. I hope, for your sake, the design is changeable.

 

[BoberFett]

See my corrected query. It will find the first day in a series of days that were missed fitting the parameters specified by the OP. It won't indicate how many days were missed, but if that was necessary it could be handled by a subquery.

And there are plenty of good reasons to write queries to determine if data is missing.

 

[WannaFly]

Boberfett: Interesting, I didn't think to write a query like that. It might work in the OP's case, but could it work if it only needed to check weekdays and not holidays?

KIAman: See my reasoning above. Also, some databases don't support Jobs naively, it'd have to be a separate app that would need to be maintained/monitored to ensure integrity to insert skipped dates.

 

[KIAman]

Originally posted by: BoberFettAnd there are plenty of good reasons to write queries to determine if data is missing.

And I agree, it just isn't flexible to operate and report on missing data. I see a lot of requirements that specify something similar and lots of development time to account for them and it always ends up being a bad initial design, which nobody ever questions because they implicitly trust the skills of the designer/analyst/architect.

 

[BoberFett]

Originally posted by: KIAman

Originally posted by: BoberFettAnd there are plenty of good reasons to write queries to determine if data is missing.

And I agree, it just isn't flexible to operate and report on missing data. I see a lot of requirements that specify something similar and lots of development time to account for them and it always ends up being a bad initial design, which nobody ever questions because they implicitly trust the skills of the designer/analyst/architect.

It may well be bad initial design when viewed later, but if you've ever worked on dragging requirements out of non-technical types you know how often it's simply out of the architects control.

 

[KB]

Originally posted by: BoberFett
Outer joins are your friend to find missing data.

select PersonID, a.LoginDate
from LoginTable a left join LoginTable b on a.CustomerID = b.CustomerID and a.LoginDate + 1 = b.LoginDate
where b.LoginDate is null

Edit: Oops, had the date add in the wrong place. a.LoginDate will be the day that the person did login and b.LoginDate will be the next day if they person did login. If they didn't, it will be null.

Thats the creativity I was looking for! Thanks. This is the query that is working for me:

select TOP 1 a.PersonID, a.LoginDate + 1
from LoginTable a left join LoginTable b on a.PersonID = b.PersonID and a.LoginDate + 1 = b.LoginDate
where a.loginDate BETWEEN '7/2/2009' AND '7/11/2009' AND b.LoginDate is null


its gets the first missing date between a date range. Changing this solution is possible, but I don't see how its such bad design. I am not going to fill in missing data to order to be able to query it. Automatically filling in 7/3/2009 with a missed flag is more work than just using this query. Luckily I don't need to worry about weekends or holidays.

 

[brandonbull]

Create a table with valid login dates for several years and then join that against your login table to find the missing dates.

 

[Kntx]

Didn't read responses. But what you should do is create a table with all dates from let's say 1/1/1990 thru 12/31/2030 and join against it to find the missing ones.