How to count number of months of row in Oracle
Solution 1:
You can use the below query having case statement to handle the status = 'COMPLETE' -
SELECT CASE WHEN COUNT(CASE WHEN status = 'COMPLETE' THEN 1 ELSE NULL END) > 0 THEN
COUNT(CASE WHEN status = 'COMPLETE' THEN 1 ELSE NULL END) + 1
ELSE NULL
END HS_TIME
FROM tb
WHERE TO_DATE(DATE_PERIOS, 'MM-YYYY') < TO_DATE('01-2020', 'MM-YYYY');
Demo.
Solution 2:
One option is to conditionally (that's the CASE
expression) add 1
(that's the SUM
function) if status
is COMPLETE.
WHERE
clause requires TO_DATE
with appropriate date format. Otherwise, you'd be comparing strings which would lead to wrong result; might be OK if date_perios
was stored in YYYYMM
format; on the other hand, perhaps you'd want to consider storing date values into the DATE
datatype column.
Sample data
SQL> with ord0011 (order_id, date_perios, status) as
2 (select 303, '4-2021' , 'COMPLETE' from dual union all
3 select 303, '5-2021' , 'COMPLETE' from dual union all
4 select 303, '10-2021', 'FALSE' from dual union all
5 select 303, '11-2021', 'COMPLETE' from dual union all
6 select 303, '12-2021', 'ACTIVE' from dual union all
7 select 303, '1-2022' , 'COMPLETE' from dual
8 )
Query itself
9 select order_id,
10 sum(case when status = 'COMPLETE' then 1 else 0 end) hs_time
11 from ord0011
12 where to_date(date_perios, 'mm-yyyy') <= to_date('&par_date', 'mm-yyyy')
13 group by order_id;
Enter value for par_date: 1-2022
ORDER_ID HS_TIME
---------- ----------
303 4
SQL> /
Enter value for par_date: 11-2021
ORDER_ID HS_TIME
---------- ----------
303 3
SQL>