Group Dates by Statuses

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?
pvsbandiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OMC2000Commented:
I guess you need something like this:

select Status, min(Start_Dt), max(End_Dt) from table_name group by Status
0
pvsbandiAuthor 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
0
OMC2000Commented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

pvsbandiAuthor Commented:
Wow! How did you get this idea?
 I'm really pleasantly surprised! Thank you :)
0
PortletPaulEE Topic AdvisorCommented:
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"
0
pvsbandiAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.