CODEWITHSUNDEEP
sqloracle-databaseoracle11g
manj
manj

Reputation: 19

How do I select start_date and corresponding end_date

The data input in the table in Oracle is as below. That is start_date is in one row but the end_date is in the next row for a account number. Want to align the start date and end date in the same row. I tried using lead function and it doesn't seem to work. I am using Oracle 11g. Can you please help me with this.

ACCT_NUM ACTV_TMST START_DATE END_DATE

1234 11/22/2006 2:12:13.928230 PM 11/22/2006 00:00:00 NULL

1234 11/28/2006 7:35:05.659595 AM NULL 11/28/2006

1234 12/22/2008 3:00:47.864811 PM 12/22/2008 00:00:00 NULL

1234 12/26/2008 3:34:28.776394 PM NULL 12/26/2008 00:00:00

1234 02/18/2016 9:22:35.746829 AM 02/18/2016 00:00:00 NULL

1234 02/23/2016 9:03:35.295622 AM NULL 02/23/2016 00:00:00

I need an output like ACCT_NUM START_DATE END_DATE

1234 11/22/2006 00:00:00 11/28/2006 00:00:00

1234 12/22/2008 00:00:00 12/26/2008 00:00:00

1234 02/18/2016 00:00:00 02/23/2016 00:00:00

Thanks.

Upvotes: 1

Views: 450

Answers (1)

sagi
sagi

Reputation: 40481

You can use ORACLE's row_number window function:

SELECT s.acct_num,
       max(s.start_date) as start_date,
       max(s.end_date) as end_date
FROM(
    SELECT t.acct_num,
           t.start_date,
           row_number() OVER(PARTITION BY t.acct_num ORDER BY t.start_date) as sd_rnk,
           t.end_date,
           row_number() OVER(PARTITION BY t.acct_num ORDER BY t.end_date) as ed_rnk
    FROM YourTable t) s
GROUP BY acct_num,
         CASE WHEN t.start_date is null then ed_rnk else sd_rnk end

This will basically rank each row, first start_date 1 , second will get 2. Same goes for end_date, first will get 1 second 2...

Then, you will group by this results (acct_num , end_date_rank / start_date_rank) and use an aggregation function to unite them into 1 row.

Upvotes: 1

Related Questions