query help - 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)

query help

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2007-01-19 : 11:33:47

I have temp table like this:

id scantime
------------------------------
1 12/1/2007 10:00:00 AM
1 12/1/2007 10:00:05 AM
1 12/1/2007 10:00:10 AM
2 12/1/2007 10:00:06 AM
3 12/1/2007 10:00:07 AM
4 12/1/2007 10:00:10 AM
1 12/1/2007 10:05:00 AM
2 12/1/1007 10:05:05 AM
3 12/1/2007 10:05:10 AM

if you observe the table. students have swiped their id's at 10:00:00 and again after 5 mins at 10:05:00.
i want to get the records of all the students who swiped their id twice, but the second swipe should be after 5 mins.
which means i want the result like this:

id scantime
------------------------------
1 12/1/2007 10:00:00 AM
2 12/1/2007 10:00:06 AM
3 12/1/2007 10:00:07 AM
1 12/1/2007 10:05:00 AM
2 12/1/1007 10:05:05 AM
3 12/1/2007 10:05:10 AM

the following records are not wanted because the swipetime is less than 5 mins for id=1 and id =4 doesnt have second swipe.

1 12/1/2007 10:00:05 AM
1 12/1/2007 10:00:10 AM
4 12/1/2007 10:00:10 AM

Any help with the query plzz?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 13:06:04

There are errors in your sample data...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 13:09:07

[code]-- prepare sample data
declare @t table (id int, scantime datetime)

insert @t
select 1, '12/1/2007 10:00:00' union all
select 1, '12/1/2007 10:00:05' union all
select 1, '12/1/2007 10:00:10' union all
select 2, '12/1/2007 10:00:06' union all
select 3, '12/1/2007 10:00:07' union all
select 4, '12/1/2007 10:00:10' union all
select 1, '12/1/2007 10:05:00' union all
select 2, '12/1/2007 10:05:05' union all
select 3, '12/1/2007 10:05:10'

-- stage the data
declare @s table (id int, scantime datetime)

-- initialize with start values for the day
insert @s
select id,
min(scantime)
from @t
group by id

-- loop until there are no more records to add
while @@rowcount > 0
insert @s
select t.id,
t.scantime
from @t as t
inner join (
select id,
max(scantime) as ms
from @s
group by id
) as s on s.id = t.id
where s.ms < dateadd(second, -299, t.scantime)

-- show the expected result
select id,
scantime
from @s
order by id,
scantime[/code]

Peter Larsson
Helsingborg, Sweden

cognos79
Posting Yak Master

241 Posts

Posted - 2007-01-19 : 13:49:11

thanks a lot peso

cognos79
Posting Yak Master

241 Posts

Posted - 2007-01-19 : 13:52:15

i have question with query: why do we have to loop

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 14:26:13

Do you have several times? That's why. It is necessare to have when checking for NEXT time later than 5 minutes apart.

I built the code based on the assumption that there could be more than one date. I prefer building algorithms that can work even if some of the original specs are changes, like more dates...

If there is only one date, the loop is only run once anyway.

Peter Larsson
Helsingborg, Sweden

cognos79
Posting Yak Master

241 Posts

Posted - 2007-01-19 : 14:33:07

thanks a lot...there is an error with the results...i dont want id=4 to appear in results bc he doesnt have a swipe after 5 minutes. i only want results for tht id if there is a swipe after 5 mins

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 14:53:34

Really? Then ID = 2 with time 10:00:06 should be removed as well, right?

-- prepare sample data
declare	@t table (id int, scantime datetime)

insert	@t
select	1, '12/1/2007 10:00:00' union all
select	1, '12/1/2007 10:00:05' union all
select	1, '12/1/2007 10:00:10' union all
select	2, '12/1/2007 10:00:06' union all
select	3, '12/1/2007 10:00:07' union all
select	4, '12/1/2007 10:00:10' union all
select	1, '12/1/2007 10:05:00' union all
select	2, '12/1/2007 10:05:05' union all
select	3, '12/1/2007 10:05:10'

-- stage the data
declare	@s table (id int, scantime datetime)

-- initialize with start values for the day
insert		@s
select		id,
		min(scantime)
from		@t
group by	id

-- loop until there are no more records to add
while @@rowcount > 0
	insert		@s
	select		t.id,
			t.scantime
	from		@t as t
	inner join	(
				select		id,
						max(scantime) as ms
				from		@s
				group by	id
			) as s on s.id = t.id
	where		s.ms < dateadd(second, -299, t.scantime)

-- delete one time only scans for a user
-- delete		s1
-- from		@s as s1
-- where		s1.id in (select s2.id from @s as s2 group by s2.id having count(*) = 1)

-- show the expected result
select		id,
		scantime
from		@s
order by	id,
		scantime

Peter Larsson
Helsingborg, Sweden

cognos79
Posting Yak Master

241 Posts

Posted - 2007-01-19 : 15:09:24

no 2 shld be there and no 4 shldnt be there...

no no...this is the scenario. say all the students swipes their cards and after sometime couple of them leave the class then the students will swipe their card again. in this case we dont have to count the students records who left the class.

did u want me to explain again???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 15:34:01

What?

Look at the sample data you provided!

select 2, '12/1/2007 10:00:06' union all
select 2, '12/1/2007 10:05:05' union all

They are not 5 minutes apart. They are 4 minutes 59 seconds apart.

select 1, '12/1/2007 10:00:00' union all
select 1, '12/1/2007 10:05:00' union all

These are exactly 5 minutes 0 seconds apart.

Peter Larsson
Helsingborg, Sweden

cognos79
Posting Yak Master

241 Posts

Posted - 2007-01-19 : 15:38:16

my bad...thanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 15:41:32

Do you have some more issued with the suggestion I provided to you?
If not, good luck!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources