Post reply

How to consolidate sequential detail rows into one summary row

  • April 20, 2012 at 12:03 pm

    #260294

    I have a table where it shows sequential actions that take place. When I have the issue happen in multiple consecutive rows i want to be able to consolidate it in one line, and show the total duration for all of the activities with only the original timestamp for that instance. The problem is that the issues are recurring in the dataset, so i cannot just use grouping because i do not want all the data grouped as 1, but clusters of the data that are sequential to be grouped. I have included below what it looks like now as well as what i would like it to look like. Any and all help is greatly appreciated.

    You will see where blue appears in two instances, once with 3 sequential rows, and once with just 1 row, showing the number of seconds that it lasted under duration. I need to be able to sum up the sequential rows into a single one so there would be 2 individual rows for blue, each with the first occuring timestamp and the total duration spent for all rows in the sequence.

    Current

    Issue row_date duration

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

    red 2012-04-01 15:40:00.000 300

    orange 2012-04-01 15:45:00.000 2580

    blue 2012-04-01 16:28:00.000 480

    blue 2012-04-01 16:36:00.000 240

    blue 2012-04-01 16:40:00.000 420

    orange 2012-04-01 16:47:00.000 5400

    yellow 2012-04-01 17:05:00.000 660

    blue 2012-04-01 17:16:00.000 2940

    violet 2012-04-01 18:05:00.000 2400

    purple 2012-04-01 18:45:00.000 22

    Needed

    Issue row_date duration

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

    red 2012-04-01 15:40:00.000 300

    orange 2012-04-01 15:45:00.000 2580

    blue 2012-04-01 16:28:00.000 1140

    orange 2012-04-01 16:47:00.000 5400

    yellow 2012-04-01 17:05:00.000 660

    blue 2012-04-01 17:16:00.000 2940

    violet 2012-04-01 18:05:00.000 2400

    purple 2012-04-01 18:45:00.000 22

    create table #group

    (

    ISSUE char(20),

    ROW_DATE datetime,

    DURATION int

    )

    insert into #group

    select 'red','2012-04-01 15:40:00.000',300

    union

    select 'orange','2012-04-01 15:45:00.000',2580

    union

    select 'blue','2012-04-01 16:28:00.000',480

    union

    select 'blue','2012-04-01 16:36:00.000',240

    union

    select 'blue','2012-04-01 16:40:00.000',420

    union

    select 'orange','2012-04-01 16:47:00.000',5400

    union

    select 'yellow','2012-04-01 17:05:00.000',660

    union

    select 'blue','2012-04-01 17:16:00.000',2940

    union

    select 'violet','2012-04-01 18:05:00.000',2400

    union

    select 'purple','2012-04-01 18:45:00.000',22

    select ISSUE,ROW_DATE,DURATION from #group

    order by ROW_DATE

    drop table #group

  • April 20, 2012 at 12:19 pm

    #1476874

    Excellent job posting ddl, sample data and desired output.

    I think you looking for something like this:

    select ISSUE, MIN(ROW_Date) as Row_Date, SUM(Duration) as Duration

    from #group

    group by Issue

    _______________________________________________________________

    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/

  • April 20, 2012 at 12:23 pm

    #1476879

    Thankyou for the quick response:-)Unfortunately this combines all of the "blues" 🙁 I still need future occurances to be seperate when they do not have an instance directly before or after.

  • April 20, 2012 at 12:27 pm

    #1476885

    Define before or after. You are going to need to find something to use to order by. Once you have figured that out need to investigate "islands". Jeff Moden has a great article about that here. http://www.sqlservercentral.com/articles/T-SQL/71550/[/url]

    Hope that helps.

    _______________________________________________________________

    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/

  • April 20, 2012 at 12:40 pm

    #1476895

    As sean, points out, without defining the order of the rows, it is impossible to group the "islands"

    To show u a sample of how it will work with the defined order, here i have included IDENTITY column and the code to produce your expected result

    Sample data:

    create table #group

    (

    id int identity,

    ISSUE char(20),

    ROW_DATE datetime,

    DURATION int

    )

    insert into #group

    select 'red','2012-04-01 15:40:00.000',300

    insert into #group

    select 'orange','2012-04-01 15:45:00.000',2580

    insert into #group

    select 'blue','2012-04-01 16:28:00.000',480

    insert into #group

    select 'blue','2012-04-01 16:36:00.000',240

    insert into #group

    select 'blue','2012-04-01 16:40:00.000',420

    insert into #group

    select 'orange','2012-04-01 16:47:00.000',5400

    insert into #group

    select 'yellow','2012-04-01 17:05:00.000',660

    insert into #group

    select 'blue','2012-04-01 17:16:00.000',2940

    insert into #group

    select 'violet','2012-04-01 18:05:00.000',2400

    insert into #group

    select 'purple','2012-04-01 18:45:00.000',22

    select * from #group

    Code:

    ; WITH CTE AS

    (

    SELECT T.ID ,t.ISSUE, t.ROW_DATE , t.DURATION

    ,(ROW_NUMBER() OVER(ORDER BY T.id) -

    ROW_NUMBER() OVER(PARTITION BY T.ISSUE ORDER BY T.id)) As Diff

    FROM #group T

    --order by t.id

    )

    SELECT T.ISSUE , MIN(T.ROW_DATE) , SUM(T.DURATION)

    FROM CTE T

    GROUP BY T.ISSUE , T.Diff

  • April 20, 2012 at 1:25 pm

    #1476918

    One little change (well, a couple of changes if you look at my setup code as well):

    create table #group

    (

    ISSUE char(20),

    ROW_DATE datetime,

    DURATION int

    );

    insert into #group

    select 'red','2012-04-01 15:40:00.000',300

    union

    select 'orange','2012-04-01 15:45:00.000',2580

    union

    select 'blue','2012-04-01 16:28:00.000',480

    union

    select 'blue','2012-04-01 16:36:00.000',240

    union

    select 'blue','2012-04-01 16:40:00.000',420

    union

    select 'orange','2012-04-01 16:47:00.000',5400

    union

    select 'yellow','2012-04-01 17:05:00.000',660

    union

    select 'blue','2012-04-01 17:16:00.000',2940

    union

    select 'violet','2012-04-01 18:05:00.000',2400

    union

    select 'purple','2012-04-01 18:45:00.000',22;

    WITH BaseData AS (

    select

    ISSUE,

    ROW_DATE,

    DURATION,

    ROW_NUMBER() OVER (ORDER BY row_date) ID

    from

    #group

    ),

    CTE AS (

    SELECT T.ID ,T.ISSUE, T.ROW_DATE , T.DURATION

    ,(ROW_NUMBER() OVER(ORDER BY T.id) -

    ROW_NUMBER() OVER(PARTITION BY T.ISSUE ORDER BY T.id)) As Diff

    FROM BaseData T

    )

    SELECT

    T.ISSUE,

    MIN(T.ROW_DATE),

    SUM(T.DURATION)

    FROM

    CTE T

    GROUP BY

    T.ISSUE,

    T.Diff

    ORDER BY

    MIN(T.ROW_DATE);

    DROP TABLE #group;

  • April 20, 2012 at 1:38 pm

    #1476926

    Thank you everyone, I think I have what I need to make this work now. I felt it would have something to do with row_number(), but it hadn't occurred to me to take the difference like this. I really appreciate everyones assistance. This has been a life saver! 🙂

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy