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>