compare row data

  • abhas

    SSCarpal Tunnel

    Points: 4679

    Hi ,

    I am having table in below format. I want to calculate difference between each two cons rows for each ID using datedifference. Date column will be a datetime. how to do that?

    below is sample data. in Date there will be a date along with time.

    ID	       Status	 Date
    111111-0010 Start 20200701
    111111-0010 Pause 20200701
    111111-0010 Resume 20200701
    111111-0010 Resume 20200702
    111111-0010 Pause 20200702
    111111-0010 Pause 20200703
    111111-0010 Resume 20200703
    111111-0010 Stop 20200703
    222222-0020 Start 20200701
    222222-0020 Pause 20200701
    222222-0020 Resume 20200701
    222222-0020 Resume 20200702
    222222-0020 Pause 20200702
    222222-0020 Pause 20200703
    222222-0020 Resume 20200703
    222222-0020 Stop 20200703

    Thanks

    Abhas

  • dkultasev

    SSC Rookie

    Points: 34

    Please provide expected result

  • abhas

    SSCarpal Tunnel

    Points: 4679

    Hi,

    Below is expected result. i want to compare each with next row on datetimefield and fetch the difference between two. Again comparision is ID wise. If next row contains different id then it will not compare. in short group on ID.

    ID	       Status	  Date	     Hours
    111111-0010 Start 20200701 2
    111111-0010 Pause 20200701 4
    111111-0010 Resume 20200701 15
    111111-0010 Resume 20200702 1
    111111-0010 Pause 20200702 1.5
    111111-0010 Pause 20200703 0.3
    111111-0010 Resume 20200703 2
    111111-0010 Stop 20200703

    • This reply was modified 1 month ago by  abhas.
    • This reply was modified 1 month ago by  abhas.
  • dkultasev

    SSC Rookie

    Points: 34

    Just small tip, if you add sample data then it is more useful for people helping you if you provide it by DDL and data inserts. In that case the person helping you would spend the time just on helping you. Anyway, this probably the script you want:

    DECLARE @table AS TABLE (
    id VARCHAR(20)
    ,[Status] VARCHAR(10)
    ,[Date] DATETIME2(7)

    )

    INSERT INTO @table
    VALUES ('111111-0010', 'Start', '20200701 00:00:00')
    , ('111111-0010', 'Pause', '20200701 02:00:00')
    , ('111111-0010', 'Resume', '20200701 06:00:00')
    , ('111111-0010', 'Resume', '20200701 21:00:00')
    , ('222222-0020', 'Start', '20200701 00:00:00')

    SELECT
    id
    ,Status
    ,Date
    ,DATEDIFF(HOUR, [Date], LEAD([Date]) OVER (PARTITION BY id ORDER BY [Date]))
    FROM @table
  • Phil Parkin

    SSC Guru

    Points: 244662

    abhas wrote:

    Hi,

    Below is expected result. i want to compare each with next row on datetimefield and fetch the difference between two. Again comparision is ID wise. If next row contains different id then it will not compare. in short group on ID.

    ID	       Status	  Date	     Hours
    111111-0010 Start 20200701 2
    111111-0010 Pause 20200701 4
    111111-0010 Resume 20200701 15
    111111-0010 Resume 20200702 1
    111111-0010 Pause 20200702 1.5
    111111-0010 Pause 20200703 0.3
    111111-0010 Resume 20200703 2
    111111-0010 Stop 20200703

    This is a joke, right? You've provided dates without any time component, making the calculation of hours impossible. You have nearly 5,000 points, you should know better.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • abhas

    SSCarpal Tunnel

    Points: 4679

    Thank you friends.

     

    Regards,

    Abhas.

  • david.edwards 76768

    SSC Eights!

    Points: 999

    Well, that's a co-incidence! I've not been in here for weeks and weeks, just posted a very convoluted post, then I see this post about comparing columns, row by row.

    Looks like the method I came up with wasn't too out there after all, as LEAD/LAG is the method recommended here 🙂

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

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