February 18, 2005 at 9:02 am
OK - another clarification. I hate to ask open ended questions of people so I have rewritten the query using "IN". This query is an example of how I am interpretting the EXISTS - and pulls back the wrong answer I was expecting.
A explanation of how I am misunderstanding EXISTS would be helpful.
SELECT t1.entrydate, t2.entrydate,
t1.location
, RIGHT(STUFF(CONVERT(CHAR(22),t1.EntryDate,13),21,4,' '),9)
, CASE
WHEN DATEDIFF(mi,t1.EntryDate,t2.EntryDate)>1440
THEN DATEDIFF(mi,t1.EntryDate,t2.EntryDate)-1440
ELSE DATEDIFF(mi,t1.EntryDate,t2.EntryDate)
END
FROM
test t1
JOIN
test t2
ON
t1.location = t2.location
AND
t1.EntryDate < t2.EntryDate
WHERE
t1.EntryDate>='20050101' AND t1.EntryDate<='20050103'
AND
t1.Location = 50
AND t1.entrydate NOT IN
(SELECT distinct t3.entrydate FROM
test t3, test t2, test t1
WHERE t3.location != 50 AND t3.EntryDate BETWEEN t1.EntryDate AND t2.EntryDate)
AND t2.entrydate NOT IN
(SELECT distinct t3.entrydate FROM
test t3, test t2, test t1
WHERE t3.location != 50 AND t3.EntryDate BETWEEN t1.EntryDate AND t2.EntryDate)
GROUP BY
t1.location
, t1.EntryDate
, t2.EntryDate
ORDER BY t1.EntryDate
This returns:
2005-01-01 10:00:00.000 2005-01-01 11:00:00.000 50 10:00:00 60
2005-01-01 10:00:00.000 2005-01-01 14:00:00.000 50 10:00:00 240
2005-01-01 10:00:00.000 2005-01-02 15:00:00.000 50 10:00:00 300
2005-01-01 11:00:00.000 2005-01-01 14:00:00.000 50 11:00:00 180
2005-01-01 11:00:00.000 2005-01-02 15:00:00.000 50 11:00:00 240
2005-01-01 14:00:00.000 2005-01-02 15:00:00.000 50 14:00:00 60
...the wrong answer.
February 18, 2005 at 10:22 am
Disregard - I got it worked out. The following routine helped illustrated it for me.
CREATE Table tblEXISTS (col1 int, col2 int)
INSERT tblEXISTS VALUES (50,10)
INSERT tblEXISTS VALUES (50,11)
INSERT tblEXISTS VALUES (45,12)
INSERT tblEXISTS VALUES (45,13)
INSERT tblEXISTS VALUES (50,14)
INSERT tblEXISTS VALUES (50,15)
select * from tblexists
select t1.col1, t1.col2, t2.col2
, CASE WHEN NOT EXISTS (SELECT * from tblexists t3 WHERE t3.col1<>50 AND t3.col2 BETWEEN t1.col2 AND t2.col2)
THEN 'TRUE' ELSE 'FALSE' END
FROM tblexists t1
INNER JOIN tblexists t2 on t1.col1=t2.col1 AND t1.col2<t2.col2
where T1.COL1=50
The t3.col1<>50 is only limiting my t3.col2 records to 12 and 13 while the t3.col2 BETWEEN t1.col2 AND t2.col2 is the true relation.
I just need to focus on the boolean return for the comparison rather than picturing a result set...
February 18, 2005 at 12:17 pm
Try this:
Step 1 - Create a temp table that holds your data fields plus an identity key. Then load the temp table with the records for the time span in question (say a week) being sure to sort in time sequence. This lets you easily compare any record to it's predecessor, where CurrKey = PriorKey+1.
Step 2 - Extract start and end times where the current location equals the prior location. For your sample data and for location 50, you get:
10:00 to 10:01
10:01 to 14:00
11:30 to 11:40
Step 3 - sum the elapsed times from step 2.
Viewing 3 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply