February 3, 2011 at 7:03 am
Good afternoon all,
I am current attempting to write a query that identifies when devices of two types (let's call them A and B) are in use by the same person at the same time. I want to list all the devices A's and then against them list all the device B's where the device B's were in use at the same time by the same person.
To put it another way, I'm trying to identify the intersects between two sets of time based data.
Here is an example of my code:
SELECT A.device_id AS ADevice,
AFitted.[datetime] AS AFittedDateTime,
ARemoved.[datetime] AS ARemovedDateTime,
BDevices.device_id AS BDevice,
BDevices.FittedDateTime AS BFittedDateTime,
BDevices.RemovedDateTime AS BRemovedDateTime
FROM dbo.devices A
LEFT OUTER JOIN dbo.CommissionedDevices AS AFitted
ON A.device_id = AFitted.device_id
AND AFitted.Operation = 0 -- Fit
LEFT OUTER JOIN dbo.CommissionedDevices AS ARemoved
ON AFitted.device_id = ARemoved.device_id
AND AFitted.person_id = ARemoved.person_id
AND DockRemoved.audit_operation = 2 -- Remove
LEFT OUTER JOIN
(
SELECT B.device_id,
BFitted.person_id,
BFitted.audit_datetime AS FittedDateTime,
BRemoved.audit_datetime AS RemovedDateTime
FROM dbo.devices B
LEFT OUTER JOIN dbo.CommissionedDevices AS BFitted
ON B.device_id = BFitted.device_id
AND BFitted.Operation = 0 -- Fit
LEFT OUTER JOIN dbo.CommissionedDevices AS BRemoved
ON BFitted.device_id = BRemoved.device_id
AND BFitted.person_id = BRemoved.person_id
AND BRemoved.audit_operation = 2 -- Remove
WHERE B.devicetype = 'B') BDevice
ON AFitted.person_id = BDevices.person_id
WHERE A.devicetype = 'A'
-- Need to get A/B Commissioning intersects
When I start specifying the WHERE clause I end up with lots of statements like:
A Starts after B but ends before B
OR A starts before B but ends after B
I am sure there must be a better way to find the intersects.
Anyone got any suggestions?
TIA,
Chris
February 3, 2011 at 7:57 am
You need two tests for datetime intersection.
If A.Start < B.End and A.End > B.Start then they intersect. This covers A starting before B and ending after B starts. It covers A being between B start and end. And so on.
- 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
February 3, 2011 at 8:15 am
GSquared,
Just tried that out and it works perfectly with my code.
Thank you so much, this was driving me nuts! 🙂
Chris
February 4, 2011 at 7:26 am
chris.king (2/3/2011)
GSquared,Just tried that out and it works perfectly with my code.
Thank you so much, this was driving me nuts! 🙂
Chris
You're welcome.
- 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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply