Comparison of times row by row

  • Hi All,

    I'm looking for some advice with comparing times in the same field. I have two tables, one table has times of sessions (multiple rows) and the other contains the session ID, start & end times of overall session block and each session length. I am looking for a way to compare from the start of the session until the end all the times sequentialy and insert or mark all the sessions that fall between 10 min intervals.

    i.e start 10:30 compare to 10:35 (no match), 10:30 to 10:40 (match 10:40 becomes new effective date), 10:40 to 10:45 (no match), 10:40 to 10:50 (match 10:50 new effective date).

    I've attempted a couple of self join solutions but simply can't seem to get what I'm after. Any advice appreciated.

    CREATE TABLE MyTable (

    [id] INT identity NOT NULL,

    [sess_id] INT NOT NULL,

    [Time] datetime NOT NULL

    )

    GO

    INSERT MyTable ( [sess_id], [Time])

    VALUES ( 9999, CAST('2013-01-01 10:30' AS datetime))

    , ( 9999, CAST('2013-01-01 10:35' AS datetime))

    , ( 9999, CAST('2013-01-01 10:40' AS datetime))

    , ( 9999, CAST('2013-01-01 10:45' AS datetime))

    , ( 9999, CAST('2013-01-01 10:50' AS datetime))

    , ( 9999, CAST('2013-01-01 11:00' AS datetime))

    , ( 9999, CAST('2013-01-01 11:10' AS datetime))

    , ( 9999, CAST('2013-01-01 11:15' AS datetime))

    , ( 9999, CAST('2013-01-01 11:20' AS datetime))

    , ( 9999, CAST('2013-01-01 11:30' AS datetime))

    CREATE TABLE MyTable2 (

    [sess_id] INT NOT NULL,

    [length] int NOT NULL,

    [startTime] datetime NOT NULL,

    [endTime] datetime NOT NULL

    )

    INSERT MyTable2 ( [sess_id],[length], [startTime],[endTime])

    VALUES ( 9999,10, CAST('2013-01-01 10:30' AS datetime),CAST('2013-01-01 11:30' AS datetime))

  • Paul Munter (9/22/2015)


    Hi All,

    I'm looking for some advice with comparing times in the same field. I have two tables, one table has times of sessions (multiple rows) and the other contains the session ID, start & end times of overall session block and each session length. I am looking for a way to compare from the start of the session until the end all the times sequentialy and insert or mark all the sessions that fall between 10 min intervals.

    i.e start 10:30 compare to 10:35 (no match), 10:30 to 10:40 (match 10:40 becomes new effective date), 10:40 to 10:45 (no match), 10:40 to 10:50 (match 10:50 new effective date).

    I've attempted a couple of self join solutions but simply can't seem to get what I'm after. Any advice appreciated.

    CREATE TABLE MyTable (

    [id] INT identity NOT NULL,

    [sess_id] INT NOT NULL,

    [Time] datetime NOT NULL

    )

    GO

    INSERT MyTable ( [sess_id], [Time])

    VALUES ( 9999, CAST('2013-01-01 10:30' AS datetime))

    , ( 9999, CAST('2013-01-01 10:35' AS datetime))

    , ( 9999, CAST('2013-01-01 10:40' AS datetime))

    , ( 9999, CAST('2013-01-01 10:45' AS datetime))

    , ( 9999, CAST('2013-01-01 10:50' AS datetime))

    , ( 9999, CAST('2013-01-01 11:00' AS datetime))

    , ( 9999, CAST('2013-01-01 11:10' AS datetime))

    , ( 9999, CAST('2013-01-01 11:15' AS datetime))

    , ( 9999, CAST('2013-01-01 11:20' AS datetime))

    , ( 9999, CAST('2013-01-01 11:30' AS datetime))

    CREATE TABLE MyTable2 (

    [sess_id] INT NOT NULL,

    [length] int NOT NULL,

    [startTime] datetime NOT NULL,

    [endTime] datetime NOT NULL

    )

    INSERT MyTable2 ( [sess_id],[length], [startTime],[endTime])

    VALUES ( 9999,10, CAST('2013-01-01 10:30' AS datetime),CAST('2013-01-01 11:30' AS datetime))

    I don't understand. Can you explain the business rules more clearly? Also it would help if you could post the desired output based on your sample data.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Apologies if I was unclear, we have a master schedule table which tells us the start time and end time of the overall schedule and the length of a session within the schedule. This table has one row per schedule, the transaction table has multiple sessions that belong to a schedule with start times but alas no end times else its a datediff where the output = length.

    We need to extract only the sessions that are the equal to the length defined on the master schedule table t. In this case we have sessions which are being generated at 5 min and 10 min intervals but we are only interested in the ones that happen 10 min apart. I've attached a screen shot of desired output.

    Any thoughts appreciated.

  • A few questions:

    1. Is Length always in minutes?

    2. Wouldn't you exclude Id 10 since that individual session starts at 11:30 which is the end of the overall session and therefore falls outside of the range?

    3. Are the session times in MyTable always rounded to five minutes? whole minutes?

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Paul Munter (9/22/2015)


    We need to extract only the sessions that are the equal to the length defined on the master schedule table. In this case we have sessions which are being generated at 5 min and 10 min intervals but we are only interested in the ones that happen 10 min apart. I've attached a screen shot of desired output.

    This is a simplistic shot at what I think you want. You did not provide a master schedule table so I made one up for testing.

    create table MasterSchedule

    (

    sess_id int,

    interval int

    )

    INSERT MasterSchedule VALUES(9999, 10)

    select * , datepart(mi,[mt.time])

    from MyTable mt

    inner join MasterSchedule m on m.sess_id = mt.sess_id

    where datepart(mi, [mt.time]) % m.interval = 0

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Quick solution

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID(N'dbo.MyTable' ) IS NOT NULL DROP TABLE dbo.MyTable ;

    IF OBJECT_ID(N'dbo.MyTable2') IS NOT NULL DROP TABLE dbo.MyTable2;

    CREATE TABLE dbo.MyTable (

    [id] INT identity NOT NULL,

    [sess_id] INT NOT NULL,

    [Time] datetime NOT NULL

    );

    CREATE TABLE dbo.MyTable2 (

    [sess_id] INT NOT NULL,

    [length] int NOT NULL,

    [startTime] datetime NOT NULL,

    [endTime] datetime NOT NULL

    );

    INSERT INTO dbo.MyTable ( [sess_id], [Time])

    VALUES ( 9999, CAST('2013-01-01 10:30' AS datetime))

    , ( 9999, CAST('2013-01-01 10:35' AS datetime))

    , ( 9999, CAST('2013-01-01 10:40' AS datetime))

    , ( 9999, CAST('2013-01-01 10:45' AS datetime))

    , ( 9999, CAST('2013-01-01 10:50' AS datetime))

    , ( 9999, CAST('2013-01-01 11:00' AS datetime))

    , ( 9999, CAST('2013-01-01 11:10' AS datetime))

    , ( 9999, CAST('2013-01-01 11:15' AS datetime))

    , ( 9999, CAST('2013-01-01 11:20' AS datetime))

    , ( 9999, CAST('2013-01-01 11:30' AS datetime));

    INSERT INTO dbo.MyTable2 ( [sess_id],[length], [startTime],[endTime])

    VALUES ( 9999,10, CAST('2013-01-01 10:30' AS datetime),CAST('2013-01-01 11:30' AS datetime));

    ;WITH BASE_DATA AS

    (

    SELECT

    MT.id

    ,MT.sess_id

    ,MT.Time

    ,MT2.startTime

    ,MT2.endTime

    ,ABS(DATEDIFF(MINUTE,MT2.startTime,MT.Time) % 10)

    + ABS(DATEDIFF(MINUTE,MT.Time,MT2.endTime) % 10) AS DECDIFF

    FROM dbo.MyTable MT

    CROSS APPLY dbo.MyTable2 MT2

    WHERE MT.sess_id = MT2.sess_id

    )

    SELECT

    BD.id

    ,BD.sess_id

    ,BD.Time

    FROM BASE_DATA BD

    WHERE BD.DECDIFF = 0;

    Results

    id sess_id Time

    ----------- ----------- -----------------------

    1 9999 2013-01-01 10:30:00.000

    3 9999 2013-01-01 10:40:00.000

    5 9999 2013-01-01 10:50:00.000

    6 9999 2013-01-01 11:00:00.000

    7 9999 2013-01-01 11:10:00.000

    9 9999 2013-01-01 11:20:00.000

    10 9999 2013-01-01 11:30:00.000

  • Length is always in minutes, thanks for the input folks very much appreciated. Will try the advice above.

Viewing 7 posts - 1 through 6 (of 6 total)

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