Simple query gone bad - 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

SQL Server Development (2000)

Simple query gone bad

Author

Topic

roblasch
Starting Member

10 Posts

Posted - 2002-12-03 : 20:57:48

I have a table with a field called Hour. It is an automated entry system and when someone logs in the hour is recorded.
So the rows have the following fields ID,Name, Hour, minute. How can I Return all of the records that have an hour entry less than or equal to 2 hours more than any record. I am trying to eliminate all records where the same person entered within 2 hours. So I want to return the first entry, but no other for the next 2 hours (for the same person). This is making me crazy(ier)

Lavos
Posting Yak Master

200 Posts

Posted - 2002-12-03 : 21:44:50

Well, you're coming up on groups and runs which is covered pretty well in Sql for Smarties, and there are some items in Henderson's Guru's Guide if I recall correctly. Let's say you have this data.


ID  NAME    Hour
 1  John     1
 2  John     2
 3  John     3
 4  John     4
 5  John     6
 6  John     8 
 7  John     11

Which records would you want returned? (Please note that I'm not sure on your exact table layout and data, if you provide some DDL to create and fill a table, more people are usually willing to help.)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-03 : 21:51:02

The answer depends on how you want to define your 2-hour periods.

If you can break up your day into 12 2-hour periods, you could then take the MIN(Hour) of each group for each person:

SELECT
ID,
Name,
Min(Hour)
FROM
table
GROUP BY ID, Name, CONVERT(Int,(Hour/2)) * 2

(not sure if you need the convert; Hour/2 may return an int -- don't have QA running on my PC at home).

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-03 : 22:03:42

I used a column with a datetime for my test you can easily combine hour and minute into a date using cast.

At first I thought the following would work

The one's within two hours

SELECT *
FROM testTable A
CROSS JOIN
testTable B
WHERE A.Name = B.Name AND A.id > B.id AND DATEDIFF ( hh , B.logtime , A.logtime ) <= 2

The one's two hours apart
SELECT *
FROM testTable A
CROSS JOIN
testTable B
WHERE A.empname = B.empname
AND A.logid < B.logid AND DATEDIFF (hh , A.logtime , B.logtime ) >= 2
ORDER BY A.logid

However I notice that the following can occur:

11:00
11:01
13:01
13:02

11:00 AND 11:01 are 1 minute apart
however 13:01 is 2 hours apart from 11:00 but not from 11:01.
Can you give an example of what you expect as a result?

Are you trying to get all records from one person where they have been logged off for more than 2 hours?

roblasch
Starting Member

10 Posts

Posted - 2002-12-04 : 08:04:27

In response to Lavos, I would want to return ID,s 1,4,8, and 11. This is a raquet club. The idea behind the query is that someone who enters the building within 2 hours is probably picking up a forgotten item or returning from their car with something, etc, not playing again.

Edited by - roblasch on 12/04/2002 08:08:00

roblasch
Starting Member

10 Posts

Posted - 2002-12-04 : 08:34:24

I Think I got it.

SELECT PT2.TranID
FROM PlayTimes AS PT INNER JOIN PlayTimes AS PT2 ON (PT.TranDate = PT2.TranDate) AND (PT.UserID = PT2.UserID)
AND
(
PT2.Hour>PT.Hour
OR
(
PT2.Hour=PT.Hour
AND
PT2.Minute>PT.Minute
)
)
AND
(
PT2.Hour-PT.Hour<2
)

Now I will have to test to make sure. Thanks for all the input.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-12-04 : 08:41:01

Couple of minor corrections.....

"In response to Lavos, I would want to return ID,s 1,4,8, and 11."

NOTE.....Lavos's "IDs" only go as far as 7.

2nd....in the following scenario.....

ID NAME Hour
1 John 1:01
2 John 2:02
3 John 3:03
4 John 4:04
5 John 5:05
6 John 6:06
7 John 7:07

7:07 is > 2 hours from 1:01...and thus qualifies for your list
and
6:06 is > 2 hours from 1:01...and thus qualifies for your list
and
5:05 is > 2 hours from 1:01...and thus qualifies for your list
and
4:04 is > 2 hours from 1:01...and thus qualifies for your list
and
3:03 is > 2 hours from 1:01...and thus qualifies for your list

thus the only one you won't get is 2:02.....which I don't think is going to be useful to you....(i know it's an extreme looking dataset, but none-the-less a possible one)....either that or you redefine your criteria....

I suspect you're trying to infer something from one set of data, ie admission card-swipe reader records....and trying to deduce that people played a game of rackets....a real solution would be to put a swipe card reader inside the racket court to power the lights and give people a 5 minute grace to tidy-up after them...

hope this helps.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources