Contigous Data Periods

  • 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.

  • 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...

  • 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