Post reply

Find Datetime Range Durations

  • August 9, 2005 at 3:08 pm

    #168264

    I used information from a topic entitled "Finding datetime range intersections and durations" to assist in getting start and end date ranges and durations to display.

    I would like to know how I can extend this to show a case where there is a start date NOT paired up with an end date (e.g. employee is still active).

    In this case I'd like the date the query is run or parameterized date to be used as the final end date in order to calculate duration.

    Sample data:

    AddressNumber DateType HistoryDate

    1169               1            6/9/1992 12:00:00 AM

    1169               0            12/5/1997 12:00:00 AM

    1169               1            8/24/2004 12:00:00 AM

    1169               0            11/26/2004 12:00:00 AM

    1169               1            7/21/2005 12:00:00 AM

    Current TSQL:

    DECLARE @T2 table (StartTime datetime,EndTime datetime,AddressNumber float,Dur real)

    INSERT INTO @T2

    SELECT  t3.Datetime AS StartTime,t3.NextDatetime AS EndTime,t3.AddressNumber,Datediff(dd,t3.Datetime,t3.NextDatetime) AS 'Dur'

    FROM (

     SELECT t1.AddressNumber , t1.HistoryDate , t1.DateType , MIN( t2.HistoryDate )

     FROM dbo.tsgvw_JDEEmployeeHireRehireView AS t1

     JOIN dbo.tsgvw_JDEEmployeeHireRehireView AS t2

     ON t1.AddressNumber = t2.AddressNumber

     WHERE t2.HistoryDate > t1.HistoryDate and t2.DateType = 0

     GROUP BY t1.AddressNumber , t1.HistoryDate , t1.DateType

              ) AS t3 ( AddressNumber , Datetime , value , NextDateTime )

    WHERE t3.value = 1

    GROUP BY t3.AddressNumber, t3.Value, t3.Datetime, t3.NextDatetime

    ORDER BY t3.Datetime ASC

    SELECT * From @T2 order By AddressNumber, StartTime

    Current Result:

    StartTime                       EndTime                        AddressNumber Dur

    1992-06-09 00:00:00.000 1997-12-05 00:00:00.000 1169               2005

    2004-08-24 00:00:00.000 2004-11-26 00:00:00.000 1169               94

    Desired Result (assuming query run on 2005-08-09):

    StartTime                       EndTime                        AddressNumber Dur

    1992-06-09 00:00:00.000 1997-12-05 00:00:00.000 1169               2005

    2004-08-24 00:00:00.000 2004-11-26 00:00:00.000 1169               94

    2005-07-21 00:00:00.000 2005-08-09 00:00:00.000 1169               19

    THANKS IN ADVANCE FOR ANY SUGGESTIONS!!!!

  • August 9, 2005 at 6:31 pm

    #580886

    -- Just small changes

    Declare @LimitDate datetime

    set @LimitDate = '20050809'

    SELECT  t3.Datetime AS StartTime,t3.NextDatetime AS EndTime,t3.AddressNumber,Datediff(dd,t3.Datetime,t3.NextDatetime) AS 'Dur'

    FROM (

     SELECT t1.AddressNumber , t1.HistoryDate , t1.DateType , isnull(MIN( t2.HistoryDate ),@LimitDate)

     FROM dbo.tsgvw_JDEEmployeeHireRehireView AS t1

      LEFT JOIN dbo.tsgvw_JDEEmployeeHireRehireView AS t2

     ON t1.AddressNumber = t2.AddressNumber and t2.HistoryDate > t1.HistoryDate

     WHERE  (t2.DateType = 0  or t2.HistoryDate is null) and t1.HistoryDate <= @LimitDate

     GROUP BY t1.AddressNumber , t1.HistoryDate , t1.DateType

              ) AS t3 ( AddressNumber , Datetime , value , NextDateTime )

    WHERE t3.value = 1

    GROUP BY t3.AddressNumber, t3.Value, t3.Datetime, t3.NextDatetime

    ORDER BY t3.Datetime ASC

     

    ---- Results:

    StartTime                       EndTime                           AddressNumber  Dur

    1992-06-09 00:00:00.000 1997-12-05 00:00:00.000    1169                2005

    2004-08-24 00:00:00.000 2004-11-26 00:00:00.000    1169                94

    2005-07-21 00:00:00.000 2005-08-09 00:00:00.000    1169                19

    (3 row(s) affected)

    hth

     


    * Noel

  • August 10, 2005 at 7:30 am

    #581012

    THANKS for the changes suggested to solve my problem.

    Performace has taken a HUGE nose dive and I am not sure why. The inner Select result named as t3 performs just fine (under 8 seconds) and returns 671 rows.

    Can anyone think of any reason why the outer Select or a data condition encountered by the outer Select would not perform well or even loop endlessly?

    Sample of Data Returned by Inner Select:

     

    BODY, TR, TD {font-Family: Trebuchet MS;font-Size:12;} .OddRow {background-Color:dddddd;Color:000000;} .EvenRow {background-Color:ccddee;Color:000000;} .Header {background-Color:222222;Color:ffffff;font-Weight:bold;}

    _x0023_AddressNumberHistoryDateTimeDateTypeNextHistoryDateTime
    110612003-02-11T00:00:00.0000000-05:0012005-08-09T00:00:00.0000000-04:00
    210762005-05-24T00:00:00.0000000-04:0012005-08-09T00:00:00.0000000-04:00
    311331992-07-09T00:00:00.0000000-04:0012004-05-07T00:00:00.0000000-04:00
    411332004-07-26T00:00:00.0000000-04:0012005-08-09T00:00:00.0000000-04:00
    511411996-12-22T00:00:00.0000000-05:0012005-08-09T00:00:00.0000000-04:00
    611452005-04-06T00:00:00.0000000-04:0012005-08-09T00:00:00.0000000-04:00
    711502005-07-29T00:00:00.0000000-04:0002005-08-09T00:00:00.0000000-04:00
    811562002-03-29T00:00:00.0000000-05:0002003-09-26T00:00:00.0000000-04:00
    911562002-10-28T00:00:00.0000000-05:0012003-09-26T00:00:00.0000000-04:00
    1011562004-04-12T00:00:00.0000000-04:0012005-08-09T00:00:00.0000000-04:00
    1111622000-02-21T00:00:00.0000000-05:0002002-08-09T00:00:00.0000000-04:00
    1211622002-08-09T00:00:00.0000000-04:0002003-10-03T00:00:00.0000000-04:00
    1311622004-04-19T00:00:00.0000000-04:0012005-08-09T00:00:00.0000000-04:00
    1411661999-04-29T00:00:00.0000000-04:0012001-04-20T00:00:00.0000000-04:00
    1511662001-04-20T00:00:00.0000000-04:0002001-09-21T00:00:00.0000000-04:00
    1611662001-07-17T00:00:00.0000000-04:0012001-09-21T00:00:00.0000000-04:00
    1711662002-10-18T00:00:00.0000000-04:0012005-08-09T00:00:00.0000000-04:00
    1811681997-02-21T00:00:00.0000000-05:0012003-09-26T00:00:00.0000000-04:00
    1911682003-11-15T00:00:00.0000000-05:0012005-08-09T00:00:00.0000000-04:00
    2011691992-06-09T00:00:00.0000000-04:0011997-12-05T00:00:00.0000000-05:00
    2111691997-12-05T00:00:00.0000000-05:0002004-11-26T00:00:00.0000000-05:00
    2211692004-08-24T00:00:00.0000000-04:0012004-11-26T00:00:00.0000000-05:00
    2311692005-07-21T00:00:00.0000000-04:0012005-08-09T00:00:00.0000000-04:00
    2411862004-01-20T00:00:00.0000000-05:0012005-08-09T00:00:00.0000000-04:00
    2512381997-07-07T00:00:00.0000000-04:0012001-10-31T00:00:00.0000000-05:00
    2612382003-11-17T00:00:00.0000000-05:0012005-08-09T00:00:00.0000000-04:00

  • August 10, 2005 at 9:36 am

    #581091

    are you saying thatthis outer wrapper :

    SELECT  t3.Datetime AS StartTime,t3.NextDatetime AS EndTime,t3.AddressNumber,Datediff(dd,t3.Datetime,t3.NextDatetime) AS 'Dur'

    FROM (

    ) AS t3 ( AddressNumber , Datetime , value , NextDateTime )

    WHERE t3.value = 1

    GROUP BY t3.AddressNumber, t3.Value, t3.Datetime, t3.NextDatetime

    ORDER BY t3.Datetime ASC

    is slow ?

    It will be difficult to check but you need to concentrate the check on the lack or absence of an index (that would be my guess!)  Have a Look at the execution plan

     


    * Noel

  • August 10, 2005 at 9:49 am

    #581098

    Yes, that is the part of the query that takes 40 minutes.

    I'll check the execution plan.

    How could I implement and index on (I think) t3 in order to improve performance?

    THANKS FOR ALL THE HELP!!!

  • August 10, 2005 at 10:01 am

    #581104

    you can perform a couple of things before I proceed:

    1.

    Can you run this:

    SELECT t1.AddressNumber , t1.HistoryDate as [DateTime] , t1.DateType as value , isnull(MIN( t2.HistoryDate ),@LimitDate) as NextDateTime

    into #T3

     FROM dbo.tsgvw_JDEEmployeeHireRehireView AS t1

      LEFT JOIN dbo.tsgvw_JDEEmployeeHireRehireView AS t2

     ON t1.AddressNumber = t2.AddressNumber and t2.HistoryDate > t1.HistoryDate

     WHERE  (t2.DateType = 0  or t2.HistoryDate is null) and t1.HistoryDate <= @LimitDate

     GROUP BY t1.AddressNumber , t1.HistoryDate , t1.DateType

    -- Mark Time

    select getdate()

    SELECT  t3.Datetime AS StartTime,t3.NextDatetime AS EndTime,t3.AddressNumber,Datediff(dd,t3.Datetime,t3.NextDatetime) AS 'Dur'

    FROM  #T3 t3

    WHERE t3.value = 1

    GROUP BY t3.AddressNumber, t3.Value, t3.Datetime, t3.NextDatetime

    ORDER BY t3.Datetime ASC

    -- Mark Time

    select getdate()

    to verify that is the second and not the first ?

     


    * Noel

  • August 10, 2005 at 12:10 pm

    #581138

    Those queries ran exceptionally fast when separated. Why?

    #1 Start:  8/10/2005 1:49:52 PM

    #1 End and #2 Start:  8/10/2005 1:50:01 PM

    #2 End:  8/10/2005 1:50:01 PM

  • August 10, 2005 at 12:19 pm

    #581141

    Do you have a lot of activity on the underlaying tables of dbo.tsgvw_JDEEmployeeHireRehireView ?

    if that is the case, with the first one you are performing a minimally logged table operation and with the second one you are getting no locks whatsover from any other users/processes

    The whole thing combined has to keep locks (shared) or wait for locks (of other process(es) throughout the length of the complete operation!

     


    * Noel

  • August 10, 2005 at 12:28 pm

    #581144

    Hmmm... does the first (inner) query complete prior to running the second (outer) query when they are combined?

    I REALLY am thankful for you assistance! I am just trying to understand what causes one approach (combined) to take almost 40 minutes and another approach (separated) to take only 8 seconds.

    I guess I need to bone up on my performance skills.

  • August 10, 2005 at 12:38 pm

    #581147

    does the first (inner) query complete prior to running the second (outer) query when they are combined?

    When combined you may get a completly different plan!! and for sure you will have to perform more operations with the "live" tables. Once you have Isolated the small set you need on a temp table you are free from lockings of other processes therefore limiting locks and with a small set a table scan is not a big deal but all this is assuming you have a lot of activity in the "live" table, if you don't then the plan that is being generated for the combined query is not performing a seek where it should and a table scan in a large set is expensive

    hth


    * Noel

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply