Sam
How can I sum(ctc) in one line with checking the 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
```
Sample Data: [dbFiddle](https://dbfiddle.uk/nCaiLhDp)
Thanks!
Top Answer
Andriy M
Here is a single statement that achieves the desired result of `2` for the date of `2023-06-01` in the given data sample:
```
SELECT
c_currmonth.GP
, c_currmonth.DATE
, SUM(c_currmonth.CTC) AS CTC_TOTAL
FROM
CUSTOMERS AS c_currmonth
INNER JOIN CUSTOMERS AS c_checkback ON 1=1
AND c_currmonth.GP = c_checkback.GP
AND c_currmonth.ID = c_checkback.ID
AND c_currmonth.DATE = c_checkback.DATE + 5 MONTHS
GROUP BY
c_currmonth.GP
, c_currmonth.DATE
;
```
I am not sure if I translated this from SQL Server correctly as I have no DB2 on my PC to check the syntax and [the DB2 engine at dbfiddle.uk][1] returns nothing for me for some reason.
I hope at least this will be useful as a starting point of sorts.
[1]: https://dbfiddle.uk/E_wuxSQe