SELECT 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 2000 Forums

Transact-SQL (2000)

SELECT query

Author

Topic

zaroblan
Starting Member

6 Posts

Posted - 2010-10-19 : 08:35:42

Greetings,

I have a table with three columns:
RowID int identity (1,1)
TimeSent datetime
EventType int

For arguments sake say the two events I am interested in are events number 4 (start) and 5 (stop)

I need to select all rows where
1) the last of either a start or stop BEFORE required date is a start
2) the first start (4) AFTER required date is a start if the last start/stop in 1) above was a stop (5)

I believe this sort of thing is possible in MySQL using the FIRST construct which is not available in SQL 2000.

Thanks,
Robert

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-19 : 08:38:02

Maybe you can help us to help you?

No, you're never too old to Yak'n'Roll if you're too young to die.

zaroblan
Starting Member

6 Posts

Posted - 2010-10-19 : 08:53:57

While I am a big fan of both Judas Priest and AC DC the response given does not help me at all.

Please could you be more specific.

Thanks.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-19 : 08:57:31

Hi zaroblan,

Webfred means that your requirement is not completly clear to us to help you. Please provide some sample data and your expected output. This will help us in understanding your requirement and we will be able to help you.

Regards,
Bohra

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-19 : 09:11:41

quote:
While I am a big fan of both Judas Priest and AC DC ...

WOW! That's great

Next time I will try to be more clear when I am asking other people to be more clear

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-19 : 09:12:53

quote:
Originally posted by pk_bohra

Hi zaroblan,

Webfred means that your requirement is not completly clear to us to help you. Please provide some sample data and your expected output. This will help us in understanding your requirement and we will be able to help you.

Regards,
Bohra

Thank you for clarification

No, you're never too old to Yak'n'Roll if you're too young to die.

zaroblan
Starting Member

6 Posts

Posted - 2010-10-19 : 09:44:10

Additonal clarification to original message:

Table

Field
UniqueId int identity (1,1)
TimeSent datetime
PersonId int
EventType int -- for this example use 4 for a start and 5 for a stop, always go from a start to a stop

Data

01,19/10/2008 10:01:00,1,1
02,19/10/2008 10:02:00,2,4
03,19/10/2008 10:03:00,3,1
04,19/10/2008 10:04:00,4,4
05,19/10/2008 10:05:00,1,5
06,19/10/2008 10:06:00,1,1 <--- REQUIRED DATE RANGE START
07,19/10/2008 10:07:00,2,5
08,19/10/2008 10:08:00,2,4
09,19/10/2008 10:09:00,1,4
10,19/10/2008 10:10:00,2,1
11,19/10/2008 10:11:00,3,4
12,19/10/2008 10:12:00,4,1
13,19/10/2008 10:13:00,1,1 <--- REQUIRED DATE RANGE END
14,19/10/2008 10:14:00,4,5
15,19/10/2008 10:15:00,4,1
16,19/10/2008 10:16:00,1,1

If my required datetime is 19/10/2010 10:06:00 what I expect to be returned is:

02,19/10/2010 10:02:00,2,4 started before range we want this one
04,19/10/2010 10:04:00,4,4 same as line above
09,19/10/2010 10:09:00,1,4 person 's last (4 or 5 before the range was a 5, so this is the time we want
00,19/10/2010 10:11:00,3,4 person 3 had no start or stop before the range, so we want the first start in the range

If latest start or stop (event 4 or event 5) BEFORE the range is a 4 we want this value
If latest start or stop (event 4 or event 5) BEFORE the range is a 5 we want the the first 4 WITHIN the range

Thanks,
Robert

yonabout
Posting Yak Master

112 Posts

Posted - 2010-10-21 : 10:26:11

Hi,

Could you tell us what the data represents in 'real life' and what you want to get from it? Is it something like a time recording system where you need to see how long users have spent doing stuff within a period?

I'm probably being stoopid

, but to me,

quote:
1) the last of either a start or stop BEFORE required date is a start

just means the last start before the required date?
And

quote:
2) the first start (4) AFTER required date is a start if the last start/stop in 1) above was a stop (5)

won't select anything because logically the result from step 1 will always be a start?

Also, sometimes you mention a single specific date, and other times date ranges. Which do you want to account for? and are you wanting to include or exclude results around the date range?

So if you could describe how you're trying to use the data in the real world it might make it a bit easier to get my head around.

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources