April 17, 2008 at 12:12 pm
I have columns as follows:
location, subloc, date/time, event, name
I need to put the following into a query but I can't get my head around it. I can get the pieces individually, but I'm having trouble drawing the comparison.
for each location and subloc find name of event "a" when event "a" precedes (event "b" with name "target") by less than 12 hours
Ideally I'd like to use parameters for the table name, location and subloc but I can worry about that after I have a way to find the name in event "a".
Any thoughts would be greatly appreciated.
April 17, 2008 at 12:39 pm
select (Column List)
from dbo.EventsTable t1
inner join dbo.EventsTable t2
on t1.Location = t2.Location
and t1.SubLoc = t2.SubLoc
and t1.[Date/Time] > t2.[Date/Time]
and t1.[Date/Time] <= dateadd(hour, 12, t2.[Date/Time])
Should end up looking something like that. The events from "t2" would be no more than 12 hours after the events from "t1".
You can add a Where clause with various parameters in it, for location, subloc, etc.
Does that give you what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 1:10 pm
Thanks for the quick reply. I shouldn't confuse the issue with "Ideally...."
This is all from one table, and I need to compare rows within the table based on the type of Event. I need to find the Name in the row when one Event occurs within 12 hours of the next, assuming that the Location and Subloc are the same for each Event and that the Name in the row for the later event is "Target"
Location Subloc Date/time Event Name
1234 b 11:15 a Joe
1234 c 12:15 a Mary
5678 b 13:15 a Bill
1234 c 14:15 b Not_Target
1234 b 14:55 b Target
My result using the above data should be "Joe" for Location 1234 and Subloc b
April 17, 2008 at 1:18 pm
Then you just need to add that stuff about "Target" to the Where clause.
Something like "Where t1.Name = 'Target' and t2.Name != 'Target'".
The query is built for using one table. The data about "t1" and "t2" are aliases for the table used in the query. You'll find that in the From clause, "From dbo.EventsTable t1", sets the "alias" for dbo.EventsTable as "t1". That way, I can have more than 1 copy of the same table in the query, which is how I built the Join so that it will return the data you want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 1:48 pm
Sorry - I missed that. This does the trick nicely.
Thanks
April 17, 2008 at 3:07 pm
Cool biz. Glad I could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply