CODEWITHSUNDEEP
sqldb2
Michal Palko
Michal Palko

Reputation: 661

Get amount per group with latest date in SQL

I have a table of monthly transactions for each project and its respective area (Revenue, cost...).

I need to get last amount by date (combination of ACCT_YR_NUM and ACCT_MO_NUM) for each of projects.

I already managed to get when was the last transaction done (column LAST_DATE) but I struggle to get its respective amount.

MY CODE:

WITH init AS (
    SELECT 
        f.PRJCT_ID,
        f.ACT_PLNRT_USD_AMT,
        f.RPRTG_AREA_NM,
        last_day(date(f.ACCT_YR_NUM || '-' || f.ACCT_MO_NUM  || '-15')) ACCT_DATE,
        MAX(last_day(date(f.ACCT_YR_NUM || '-' || f.ACCT_MO_NUM  || '-15'))) over (partition by f.PRJCT_ID, f.RPRTG_AREA_NM) AS LAST_DATE
    FROM my.table f
)

SELECT 
    *,
    MAX(ACT_PLNRT_USD_AMT) OVER (PARTITION BY ACCT_DATE = LAST_DATE, RPRTG_AREA_NM, PRJCT_ID) AS MAX_DATE_AMT
FROM init 

ORDER BY 
    PRJCT_ID,
    RPRTG_AREA_NM,
    ACCT_DATE

RESULT (WRONG):

PRJCT_ID RPRTG_AREA_NM ACT_PLNRT_USD_AMT ACCT_DATE LAST_DATE MAX_DATE_AMT
009RR0S BACKLOG 2587543 6/30/2022 9/30/2022 2795163
009RR0S BACKLOG 2507543 9/30/2022 9/30/2022 2507543
009RR0S EXPENSE 1056 6/30/2022 6/30/2022 1056
009RR0S REVENUE 21120 6/30/2022 6/30/2022 21120
009RR0S SIGNINGS -202540 1/31/2021 1/31/2022 -202540
009RR0S SIGNINGS -266500 1/31/2022 1/31/2022 -266500
00GRQ6W COST 17169 1/31/2021 2/28/2021 17169
00GRQ6W COST -17169 2/28/2021 2/28/2021 -17169

EXPECTED RESULT:

PRJCT_ID RPRTG_AREA_NM ACT_PLNRT_USD_AMT ACCT_DATE LAST_DATE MAX_DATE_AMT
009RR0S BACKLOG 2587543 6/30/2022 9/30/2022 2507543
009RR0S BACKLOG 2507543 9/30/2022 9/30/2022 2507543
009RR0S EXPENSE 1056 6/30/2022 6/30/2022 1056
009RR0S REVENUE 21120 6/30/2022 6/30/2022 21120
009RR0S SIGNINGS -202540 1/31/2021 1/31/2022 -266500
009RR0S SIGNINGS -266500 1/31/2022 1/31/2022 -266500
00GRQ6W COST 17169 1/31/2021 2/28/2021 -17169
00GRQ6W COST -17169 2/28/2021 2/28/2021 -17169

SOURCE SAMPLE TABLE (my.table):

PRJCT_ID RPRTG_AREA_NM ACT_PLNRT_USD_AMT ACCT_YR_NUM ACCT_MO_NUM
009RR0S BACKLOG 2507543 2022 06
009RR0S BACKLOG 2507543 2022 09
009RR0S EXPENSE 1056 2022 06
009RR0S REVENUE 21120 2022 06
009RR0S SIGNINGS -202540 2021 01
009RR0S SIGNINGS -266500 2022 01
00GRQ6W COST 17169 2021 01
00GRQ6W COST -17169 2021 02

Upvotes: 0

Views: 39

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

The idea is to enumerate rows in each group to assign row number 1 to a row with the latest date in this group and use this row as the only one in the MAX OLAP function.

WITH 
MY_TABLE (PRJCT_ID, RPRTG_AREA_NM, ACT_PLNRT_USD_AMT, ACCT_YR_NUM, ACCT_MO_NUM) AS
(
VALUES
  ('009RR0S', 'BACKLOG' , 2507543, '2022', '06')
, ('009RR0S', 'BACKLOG' , 2507543, '2022', '09')
, ('009RR0S', 'EXPENSE' ,    1056, '2022', '06')
, ('009RR0S', 'REVENUE' ,   21120, '2022', '06')
, ('009RR0S', 'SIGNINGS', -202540, '2021', '01')
, ('009RR0S', 'SIGNINGS', -266500, '2022', '01')
, ('00GRQ6W', 'COST'    ,   17169, '2021', '01')
, ('00GRQ6W', 'COST'    ,  -17169, '2021', '02')
)
, INIT AS
(
SELECT 
  F.*
, DATE (ACCT_YR_NUM || '-' || ACCT_MO_NUM || '-01') + 1 MONTH - 1 DAY AS ACCT_DATE
, ROW_NUMBER () OVER (PARTITION BY PRJCT_ID, RPRTG_AREA_NM ORDER BY ACCT_YR_NUM DESC, ACCT_MO_NUM DESC) AS RN_
FROM MY_TABLE F
)
SELECT 
  PRJCT_ID, RPRTG_AREA_NM, ACT_PLNRT_USD_AMT, ACCT_DATE
, MAX (ACCT_DATE)                                  OVER (PARTITION BY PRJCT_ID, RPRTG_AREA_NM) AS LAST_DATE
, MAX (CASE RN_ WHEN 1 THEN ACT_PLNRT_USD_AMT END) OVER (PARTITION BY PRJCT_ID, RPRTG_AREA_NM) AS MAX_DATE_AMT
FROM INIT
ORDER BY PRJCT_ID, RPRTG_AREA_NM, ACCT_DATE
PRJCT_ID RPRTG_AREA_NM ACT_PLNRT_USD_AMT ACCT_DATE LAST_DATE MAX_DATE_AMT
009RR0S BACKLOG 2507543 2022-06-30 2022-09-30 2507543
009RR0S BACKLOG 2507543 2022-09-30 2022-09-30 2507543
009RR0S EXPENSE 1056 2022-06-30 2022-06-30 1056
009RR0S REVENUE 21120 2022-06-30 2022-06-30 21120
009RR0S SIGNINGS -202540 2021-01-31 2022-01-31 -266500
009RR0S SIGNINGS -266500 2022-01-31 2022-01-31 -266500
00GRQ6W COST 17169 2021-01-31 2021-02-28 -17169
00GRQ6W COST -17169 2021-02-28 2021-02-28 -17169

Upvotes: 1

Related Questions