BI Publisher: how to use first and last aggregation

Question

I have data:

and I want to show the data like this:

i try to use group level link in order to use aggregation function but it's not working as i expect.

SQL Script that i want show data:

SELECT A.ITM_ID

,B.TRNS_TYP

--,B.TRNS_DTE

,MIN(B.QTY) KEEP (DENSE_RANK FIRST ORDER BY A.ITM_ID,B.TRNS_TYP,B.TRNS_DTE) QTY

,max(b.amount) keep (dense_rank last order by A.ITM_ID,B.TRNS_TYP,B.TRNS_DTE) amt

FROM V_PRODUCT A

JOIN TMP_INV B

ON A.ITM_ID=B.ITM_ID

GROUP BY

A.ITM_ID

,B.TRNS_TYP

--,B.TRNS_DTE

order by 1,2;

Please suggest me.

Thanks in advance.

Thomas Dodds · Answer

+1 to @Joel Acha -

here is effectively what you want in SQL ...

SELECT *

FROM

(

--BEG rows

SELECT B.PSTL_CDE,

A.ITM_ID,

B.TRNS_TYP,

MAX(B.QTY) KEEP (DENSE_RANK LAST ORDER BY B.TRNS_DTE DESC) OVER (PARTITION BY B.PSTL_CDE, A.ITM_ID, B.TRNS_TYP) FIRST_QTY,

MAX(B.AMOUNT) KEEP (DENSE_RANK LAST ORDER BY B.TRNS_DTE ASC) OVER (PARTITION BY B.PSTL_CDE, A.ITM_ID, B.TRNS_TYP) LAST_AMOUNT

FROM V_PRODUCT A

JOIN TMP_INV B

ON A.ITM_ID      =B.ITM_ID

WHERE B.TRNS_TYP = 'BEG'

UNION

-- END rows

SELECT B.PSTL_CDE,

A.ITM_ID,

B.TRNS_TYP,

MAX(B.QTY) KEEP (DENSE_RANK LAST ORDER BY B.TRNS_DTE DESC) OVER (PARTITION BY B.PSTL_CDE, A.ITM_ID, B.TRNS_TYP) FIRST_QTY,

MAX(B.AMOUNT) KEEP (DENSE_RANK LAST ORDER BY B.TRNS_DTE ASC) OVER (PARTITION BY B.PSTL_CDE, A.ITM_ID, B.TRNS_TYP) LAST_AMOUNT

FROM V_PRODUCT A

JOIN TMP_INV B

ON A.ITM_ID      =B.ITM_ID

WHERE B.TRNS_TYP = 'END'

)

ORDER BY 1,2,3

I know it seems weird to use MAX() in the FIRST rows, but notice the sort order is flipped in FIRST versus LAST (ASC in LAST is optional - just wanted to show you the difference) --- I have also added the PARTITION BY   ... then I wrapped the select to put them all together in the right order ...

Joel Acha · Answer

Something along the lines of:

QTY would be: FIRST(QTY BY PSTL_CDE,ITM_ID,TRNS_TYP)

AMOUNT would be: LAST(AMOUNT BY PSTL_CDE,ITM_ID,TRNS_TYP)