Search code examples
sqldb2db2-400db2-luw

How to check active IDs from six months ago

How can I sum(ctc) in one line with a single statement that IDs from 1/1/2023 still exist on 6/1/2023? so I tried the below statement but returned 0!

SUM( CASE WHEN ID = LAG(ID) OVER (PARTITION BY GP ORDER BY Date- 5 MONTH) 
THEN SUM(FLOAT(value)) ELSE 0 END) AS count

I also tried this :

case when date = Date- 5 MONTH then 
        coalesce(
            lag(value) over(
                partition by id, case when date = eDate- 5 MONTH then 1 else 0 end
                order by date
            ),
            0
        )
    end  AS PrevValue

dbfiddle Link to sample data!

Desired Result

Solution

  • Use a correlated subquery like here:

    WITH CUSTOMERS AS 
(
SELECT GP, ID, DATE(TO_DATE(DATE, 'MM/DD/YYYY')) AS DATE, CTC
FROM
(
VALUES
  ('AAAA', '001', '1/1/2023', 1)
, ('AAAA', '002', '1/1/2023', 1)
, ('AAAA', '003', '1/1/2023', 1)
, ('AAAA', '004', '1/1/2023', 0)
, ('AAAA', '001', '6/1/2023', 1)
, ('AAAA', '002', '6/1/2023', 1)
, ('AAAA', '005', '6/1/2023', 1)
, ('AAAA', '006', '6/1/2023', 1)
) CUSTOMERS (GP, ID, DATE, CTC)
)
SELECT 
  A.*
, A.CTC +
  COALESCE 
  (
    (
      SELECT SUM(B.CTC) 
      FROM CUSTOMERS B 
      WHERE B.ID = A.ID AND B.DATE = A.DATE - 5 MONTH
     )
   , 0
  ) AS CTC_SUM
FROM CUSTOMERS A
    GP ID DATE CTC CTC_SUM
    AAAA 001 2023-01-01 1 1
    AAAA 002 2023-01-01 1 1
    AAAA 003 2023-01-01 1 1
    AAAA 004 2023-01-01 0 0
    AAAA 001 2023-06-01 1 2
    AAAA 002 2023-06-01 1 2
    AAAA 005 2023-06-01 1 1
    AAAA 006 2023-06-01 1 1

    fiddle