How to get calculated values from a derived(?) table?

  • Here is my filtered dataset:

    Screenshot 2024-06-24 183514

    I would like to calculate the time difference between each 0 and 1 that occur sequentially.  In this case, the time difference between each time stamp, since the 0's and 1's alternate.  So, from item 10 to 11 - that's just over 25 minutes, from 11 to 12 - almost two minutes.  How would I go about this?

    I mention derived in the title because I'm thinking I might need to use some sort of subquery/derived table?  I don't have much experience on derived tables and how to pull out a reference to each row to find the time difference.

    Thank you

    • This topic was modified 4 weeks ago by  mjdemaris. Reason: missing info
  • use LAG()? I tried to use your picture to create a table, but SSMS didn't know what to do with the picture when I tried to paste it in.

    In all seriousness, if you want help, post consumable data.

  • Please see the article at the first link in my signature line below.  Most of us like to test our answers before we publish them.  For that, we need "Readily Consumable" Test Data and that article explains one of a couple of ways to do it.  Thanks for helping us help you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • oh, the 25 minutes. that's the giveaway. use LAG, like I said.

    use tempdb;
    go

    CREATE TABLE ds(
    ID INT NOT NULL,
    Flag BIT,
    EventTime DATETIME2
    );
    GO
    INSERT INTO ds VALUES
    (10,0,'2024-06-24 14:20:19.437'),
    (11,1,'2024-06-24 14:45:45.027'),
    (12,0,'2024-06-24 14:47:35.067'),
    (13,1,'2024-06-24 15:14:45.653'),
    (14,0,'2024-06-24 15:17:00.707'),
    (15,1,'2024-06-24 15:27:35.937'),
    (16,0,'2024-06-24 15:29:35.980');
    SELECT ppt.ID, ppt.Flag, ppt.EventTime,ppt.PrevTime,ppt.PrevPrevTime,
    span=DATEDIFF(minute,ppt.PrevTime,ppt.EventTime)
    FROM
    (SELECT ID, Flag, EventTime
    ,PrevTime = LAG(EventTime,1) OVER (ORDER BY EventTime)
    ,PrevPrevTime = LAG(EventTime,2) OVER (ORDER BY EventTime)
    FROM ds) ppt

     

  • Thanks Jeff, I will be sure to have some ready to use code in the future.

  • So, this is a derived table and LAG() is a window function.  Well, I just started learning about these and I appreciate the help.  I don't know enough about window functions to really know what I can or should do with them.  But now I have one more tool in my toolbox!

    Thank you

  • You're not going to use this data as points in time, but as intervals, so model it that way

    Create table events

    (event_number integer not null primary key,

    event_start_timestamp dateime2(0) not null unique,

    event_finish_timestamp dateime2(0) unique,

    check (event_start_timestamp <= event_finish_timestamp) );

    It's trivial to compute the duration of each of the events now. After 30 something years of doing SQL, I found it's not a good idea to write with assembly language, BIT flags like you're doing.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • This "check" field, is this something that you create as a field in a table?  Because I've never seen that done.

    What is the reasoning behind not using BIT flags?  How could they be detrimental?

  • jcelko212 32090 wrote:

    You're not going to use this data as points in time, but as intervals, so model it that way

    Create table events (event_number integer not null primary key, event_start_timestamp dateime2(0) not null unique, event_finish_timestamp dateime2(0) unique, check (event_start_timestamp <= event_finish_timestamp) );

    It's trivial to compute the duration of each of the events now. After 30 something years of doing SQL, I found it's not a good idea to write with assembly language, BIT flags like you're doing.

    True enough but sometimes... most of the time... you have to deal with the data that you're given.  With that being said, do you have some code that will convert the OP's original data to the condition that you're describing?

    After all, you DID say it was  a "trivial" thing to do and so it should be a breeze for someone with over 30 years of experience in SQL, like yourself.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mjdemaris wrote:

    Thanks Jeff, I will be sure to have some ready to use code in the future.

    Heh.. "next time".  You're saying that because someone already did it but... they did a datatype incorrectly (IMHO), which also complicates the solution a lot as well as negating some future functionality that you might be asked to do like determining what the percentage of time that the EventStatus was a 1 and how much total time was that... by day, by week, by, month, by quarter, by year?

    If you look at the DATA for the "EventTime", we see that the milliseconds ALWAYS end in a 0, 3, or 7.  There's only one datatype that does that and that's the DATETIME datatype.  The person that posted code used DATETIME2.  There's a HUGE difference in functionality between the 2 datatypes with DATETIME2 being the more complex of the 2 for the calculation of simple DURATIONs.  That's why he resorted to calculating just MINUTES.

    Here's an article on how to calculate duration for DATETIME datatypes.

    https://www.sqlservercentral.com/articles/calculating-duration-using-datetime-start-and-end-dates-sql-spackle-2

    Ok... so let's build some test data with what I believe is the correct datatype for the Event times stamp (DATETIME).

    --===== Create the test table with the expected Clustered PK.
    DROP TABLE IF EXISTS #Event
    ;
    CREATE TABLE #Event
    (
    EventID INT NOT NULL
    ,EventState BIT NOT NULL
    ,EventTS DATETIME NOT NULL
    ,PRIMARY KEY CLUSTERED (EventTS,EventID)
    )
    ;
    --===== Populate the test table
    INSERT INTO #Event
    (EventID,EventState,EventTS)
    VALUES (10,0,'2024-06-24 14:20:19.437')
    ,(11,1,'2024-06-24 14:45:45.027')
    ,(12,0,'2024-06-24 14:47:35.067')
    ,(13,1,'2024-06-24 15:14:45.653')
    ,(14,0,'2024-06-24 15:17:00.707')
    ,(15,1,'2024-06-24 15:27:35.937')
    ,(16,0,'2024-06-24 15:29:35.980')
    ,(17,1,'2024-06-26 17:14:13.653') -- > 48 hours later
    ;

    In the following, it's easy to calculate the duration just by subtracting one EventTS from another.  We "DRY" that out in a CTE and then formatting becomes nearly trivial and the number of hours can go up to whatever DATEDIFF can handle.  Again, this is made pretty easy because of the use of the DATETIME datatype.

    The cool part about this code is that, not only is it simple, it makes things like "DecimalDaysDuration" lead to a whole lot of other possibilities including temporal aggregations really easy (read the comments in the code).  You'll become an "analyst hero" in the eyes of others.

    --===== Solution uses LEAD to provide the duration for the current EventState.
    -- For example, EventID 10 recorded an EventState of "0" that started at
    -- '2024-06-24 14:20:19.437'. That EventState lasted until the next EventID,
    -- which changed the EventState to "1" and that started at '2024-06-24 14:45:45.027'.
    -- That means that the EventState that started at EventID "10" lasted for
    -- '0:25:25:590" (hh:mi:ss:mmm).
    --
    -- Note also how easy it is to convert the RawDur to DecimalDays so that you
    -- could easily aggregate durations by EventState and EventTS or whatever you
    -- want than then simply do a CONVERT(DATETIME,SUM(DecimalDays)) and format that
    -- like we did for the EventDuration to get hhhhhhh:mi:ss:mmm displayed for that
    -- duration aggregate
    WITH cteDryDur AS
    (
    SELECT EventID,EventState,EventTS
    ,RawDur = LEAD(EventTS,1) OVER (ORDER BY EventTS,EventID)-EventTS
    FROM #Event
    )
    SELECT EventID,EventState,EventTS
    ,EventDuration = STUFF(CONVERT(VARCHAR(20),RawDur,114),1,2,DATEDIFF(hh,0,RawDur))
    ,DecimalDaysDuration = CONVERT(FLOAT,RawDur)
    FROM cteDryDur
    ORDER BY EventTS,EventID
    ;

    That produces the following output...

    ... and that, my friend, is the easy power of DATETIME and the reason why you shouldn't wait even when it looks like someone has done the job for you. 😀

    Since you say you're a bit new, lookup the documentation for each function used by searching for the function name followed by t-sql.  Like this...

    STUFF t-sql

    For the arcane features of DATETIME, Microsoft unfortunately listened to some "experts" and they no longer carry the information in their documentation.  A lot of the "experts" are also authors on the internet and so it's a bit difficult to find decent articles with the old knowledge that is still very true and useful. 🙁

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mjdemaris wrote:

    This "check" field, is this something that you create as a field in a table?  Because I've never seen that done.

    What is the reasoning behind not using BIT flags?  How could they be detrimental?

    It's not a "check" field, it's a check constraint.

    JCelko is a SQL purist.  Some of his comments are based on his ideal SQL rather than the T-SQL that this forum is dedicated to.  He always denigrates the use of BIT flags, but never discusses the suggested alternative of using Boolean data types, because he knows full well that T-SQL DOES NOT SUPPORT BOOLEAN DATA TYPES.  Everything that JCelko says needs to be taken with a huge grain of salt, because of his refusal to ground his recommendations in reality.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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