SELECT (
SELECT DATE'2021-08-31' - MIN(DATE)
FROM TABLE
WHERE BAL LIKE '237%'
) AS DATE_DIF,
( SELECT SUMM
FROM TABLE
WHERE DAT = DATE'2021-08-31' AND BAL LIKE '237%'
) AS SALDO_PASSIVE
FROM DUAL;
Output is like
DATE_DIFF | SALDO_PASSIVE |
---|---|
837 | 3263200 |
I have tried to use CASE before SUMM but it did not work. Here Date difference = 837 it is between 2-3 years, So output should be SUMM * 0,4. Or is there another way to do that?
CodePudding user response:
You have only mentioned the logic if year is between 2 and 3. If there are other conditions then you need to mention in case statement.
select
DATE_DIF,
(case DATE_DIF/365 between 2 and 3 then SALDO_PASSIVE*.04 else SALDO_PASSIVE end ) SALDO_PASSIVE
(
SELECT (
SELECT DATE'2021-08-31' - MIN(DATE)
FROM TABLE
WHERE BAL LIKE '237%'
) AS DATE_DIF,
( SELECT SUMM
FROM TABLE
WHERE DAT = DATE'2021-08-31' AND BAL LIKE '237%'
) AS SALDO_PASSIVE
FROM DUAL
);