CODEWITHSUNDEEP
sqloracle-database
BoogieMan2718
BoogieMan2718

Reputation: 109

Oracle SQL Return First & Last Value From Different Columns By Partition

I need help with a query that will return a single record per partition in the below dataset. I used the DENSE_RANK to get the order and first/last position within each partition, but the problem is that I need to get a single record for each EMPLOYEE ITEM_ID combination which contains:

Here is the initial data table, the same data table ordered with rank, and the desired result at the end (see image below):

Also, here is the code used to get the ordered table with rank values:

SELECT T.*,
    DENSE_RANK() OVER (PARTITION BY T.EMPLOYEE, T.ITEM_ID ORDER BY T.START) AS D_RANK
FROM TEST_DATA T
ORDER BY T.EMPLOYEE, T.ITEM_ID, T.START;

Data Before & After

Upvotes: 0

Views: 679

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Use first/last option to find statuses. The rest is classic aggregation: 

select employee, min(start_), sum(duration),
       max(init_status) keep (dense_rank first order by start_),
       max(fin_status)  keep (dense_rank last  order by start_)
  from test_data t
  group by employee, item_id
  order by employee, item_id;

start is a reserved word, so I used start_ for my test.

Upvotes: 2

Related Questions