BI Publisher: how to use first and last aggregation

3023281

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.

Joel

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)

Thomas Dodds

+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 ...