compare row data

  • 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-0010Start20200701
    111111-0010Pause20200701
    111111-0010Resume20200701
    111111-0010Resume20200702
    111111-0010Pause20200702
    111111-0010Pause20200703
    111111-0010Resume20200703
    111111-0010Stop20200703
    222222-0020Start20200701
    222222-0020Pause20200701
    222222-0020Resume20200701
    222222-0020Resume20200702
    222222-0020Pause20200702
    222222-0020Pause20200703
    222222-0020Resume20200703
    222222-0020Stop20200703

    Thanks

    Abhas

  • Please provide expected result

  • 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-0010Start202007012
    111111-0010Pause202007014
    111111-0010Resume2020070115
    111111-0010Resume202007021
    111111-0010Pause202007021.5
    111111-0010Pause202007030.3
    111111-0010Resume202007032
    111111-0010Stop20200703

    • This reply was modified 4 years, 4 months ago by  abhas.
    • This reply was modified 4 years, 4 months ago by  abhas.
  • 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
  • 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-0010Start202007012
    111111-0010Pause202007014
    111111-0010Resume2020070115
    111111-0010Resume202007021
    111111-0010Pause202007021.5
    111111-0010Pause202007030.3
    111111-0010Resume202007032
    111111-0010Stop20200703

    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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thank you friends.

     

    Regards,

    Abhas.

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

    "Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
    — Samuel Johnson
    I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

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

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