Puzzling query - 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 2005 Forums

Transact-SQL (2005)

Puzzling query

Author

Topic

666arc
Starting Member

3 Posts

Posted - 2007-08-14 : 15:35:20

Hi all

I've been asked to look at pulling back admitttance times to a club but to ignore certain spurious rows. I have a table like this

MemberNo Club TimeIn
1 A 2007-07-01 08:00:00.000
2 B 2007-08-12 09:10:35.000
2 B 2007-08-12 09:10:45.000
3 C 2007-08-13 10:00:00.000
3 C 2007-08-13 18:00:00.000
etc

You can see that member 2 swiped in and then re-swiped in again 10sec later - I want this to count as one visit for that but member 3 swiped in at 10am then again at 6pm on the same day - I want this to count as two visits that day. ie class a seperate visit where the swipe time on each day is 4 hours or more apart.

Can anyone help point me in the right direction.

Thanks

Andy

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-14 : 16:37:28

[code]
Declare @T Table (MemberNo int, Club char(1), TimeIn datetime)
Insert into @t
Select 1, 'A', '2007-07-01 08:00:00.000' union all
Select 2, 'B', '2007-08-12 09:10:35.000' union all
Select 2, 'B', '2007-08-12 09:10:45.000' union all
Select 3, 'C', '2007-08-13 10:00:00.000' union all
Select 3, 'C', '2007-08-13 18:00:00.000'

Select a.Memberno, a.club, count1 = Sum(Case When NextTimeDiff <= 14400 Then 0 else 1 end)
from (
Select T.Memberno, T.Club
,NextTimeDiff = datediff(ss, (Select Min(TimeIn) From @T T2 Where T2.Memberno = T.Memberno AND T2.Club = T.Club and T2.Timein < T.TimeIn ), T.timein)
from @t T
) A
Group by A.Memberno, A.Club
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-14 : 16:37:39

It would help if you started by defining exactly what the rules are for which rows you want to exclude, and to give a good set of examples.

CODO ERGO SUM

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 16:51:27

what should be the time difference in the 2 swipes to count that as one visit or 2 different visits. you need to consider all scenarios to make sure you won't get any bugs later

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-14 : 17:04:14

quote:
Originally posted by ashley.sql

what should be the time difference in the 2 swipes to count that as one visit or 2 different visits. you need to consider all scenarios to make sure you won't get any bugs later

Ashley Rhodes

Actually he did mention 4 hrs.

quote:
Originally posted by 666arc

You can see that member 2 swiped in and then re-swiped in again 10sec later - I want this to count as one visit for that but member 3 swiped in at 10am then again at 6pm on the same day - I want this to count as two visits that day. ie class a seperate visit where the swipe time on each day is 4 hours or more apart.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

666arc
Starting Member

3 Posts

Posted - 2007-08-15 : 11:28:04

Dinakar

Thanks very much for your time but unfortunately it didn't work when I tried it on live data. I got a count of 2 for some member/club records where the second swipe was within 4 hours of the first :

This is for one member from one club and also shows swipe time :
Member Club Swipe Time Count
149578 59 2003-02-20 07:19:00.000 1
149578 59 2003-02-21 07:11:00.000 1
149578 59 2003-02-22 09:52:00.000 1
149578 59 2003-02-24 07:13:00.000 1
149578 59 2003-02-28 07:24:00.000 1
149578 59 2003-03-04 07:21:00.000 1
149578 59 2003-03-05 07:22:00.000 1
149578 59 2003-03-07 07:07:00.000 1 **
149578 59 2003-03-07 07:08:00.000 1 **
149578 59 2003-03-11 07:20:00.000 1
149578 59 2003-03-14 07:02:00.000 1
149578 59 2003-03-15 09:48:00.000 1
149578 59 2003-04-09 07:23:00.000 1
149578 59 2003-04-15 07:06:00.000 1
149578 59 2003-04-26 10:09:00.000 1
149578 59 2003-04-29 07:04:00.000 1
149578 59 2003-05-03 09:59:00.000 1
149578 59 2003-05-10 10:14:00.000 1
149578 59 2003-06-07 10:24:00.000 1
149578 59 2003-06-28 10:32:00.000 1
149578 59 2003-07-02 07:29:00.000 1
149578 59 2003-07-05 10:28:00.000 1
149578 59 2003-07-25 07:23:00.000 1
149578 59 2003-07-26 10:26:00.000 1
149578 59 2003-08-02 10:29:00.000 1
149578 59 2003-08-09 10:27:00.000 1
149578 59 2003-08-16 10:30:00.000 1
149578 59 2003-09-06 10:31:00.000 1
149578 59 2003-10-04 10:34:00.000 1
149578 59 2004-11-29 07:22:17.477 1 **
149578 59 2004-11-29 07:22:22.417 1 **

I've put asterisks next to the lines concerned but I will only want to return one of these rows not both.

Thanks again

Andy

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-15 : 11:41:47

The dates dont seem to be in order. If I use only those 2 records in the dataset I get the expected count of 1.


Declare @T Table (MemberNo int, Club varchar(10), TimeIn datetime)
Insert into @t
Select 149578, '59', '2003-03-07 07:07:00.000'   union all
Select 149578, '59', '2003-03-07 07:08:00.000'  


Select a.Memberno, a.club, count1 = Sum(Case When NextTimeDiff <= 14400 Then 0 else 1 end)
from (
	Select T.Memberno, T.Club
		,NextTimeDiff = datediff(ss, (Select Min(TimeIn) From @T T2 Where T2.Memberno = T.Memberno AND T2.Club = T.Club and T2.Timein < T.TimeIn ), T.timein)
	from @t T
) A 
Group by A.Memberno, A.Club

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 13:43:42

my bad did not read that part carefully

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources