Group Dates by Statuses

pvsbandi
pvsbandi used Ask the Experts™
on
Hi,

 We are on Postgresql 9.6
I have a table with fields like this:

   ID                 Status                    Start_Dt                     End_Dt
    1                    AP                        01/01/2018                01/31/2018
    2                    AP                        02/01/2018                02/28/2018
    3                    AP                        03/01/2018                03/31/2018
    4                    AP                        04/01/2018                 04/30/2018
    5                    DE                        05/01/2018                05/31/2018
    6                    AP                         06/01/2018               06/30/2018
     7                   AP                         07/01/2018                07/31/2018
    8                    CS                          08/01/2018               08/31/2018

I need the result like the following:

     Status                        Start_Dt                     End_Dt
        AP                         01/01/2018            04/30/2018
        DE                         05/01/2018            05/31/2018
        AP                          06/01/2018            07/31/2018
        CS                          08/01/2018            08/31/2018

Basically, need to stitch up the dates based on the change in  the Status.

Can someone please help with the solution?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I guess you need something like this:

select Status, min(Start_Dt), max(End_Dt) from table_name group by Status

Author

Commented:
But that will group all 'AP' together, for example.
  I need the statuses based on their change.
So, a status can come again when grouped
Well, in this case it's a bit more comples:

select status, min(Start_dt) Start_dt, max(End_dt) End_dt
     from (select t.*, min(id) over (partition by Status, grp) as minid
      from (select t.*,
                   (row_number() over (order by id) - row_number()
over (partition by Status order by id)
                   ) as grp
            from t1 t
           ) t
     ) t
    group by status, minid order by 2

Open in new window

Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Author

Commented:
Wow! How did you get this idea?
 I'm really pleasantly surprised! Thank you :)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
TIP: To understand how this technique works, I suggest you run the following:

SELECT
      t.*
    , ROW_NUMBER() OVER (ORDER BY id) as r1
    , ROW_NUMBER() OVER (PARTITION BY Status ORDER BY id) as r2
    ,(ROW_NUMBER() OVER (ORDER BY id) 
    - ROW_NUMBER() OVER (PARTITION BY Status ORDER BY id)
      ) AS grp
FROM t1 t

Open in new window


The first row_number sequence just keeps incrementing
The second restarts at 1 for each change in status
Because both increment by 1 in  unison, the difference between them will be a constant for each "grp"

Author

Commented:
Paul, thanks for chiming in.
I initially had to think hard about the approach.
But as I broke it to minor pieces, got the gist and was happy to see a simple method than using a procedure block.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial