How to count number of months of row in Oracle-CodePudding

Im using ORACLE TO check condition in 'DATE_PERIOS' with current time and last time

my ORD0011 table:

-------------------------------------------------------------
ORDER_ID  |     BS_NO   |    DATE_PERIOS   |    STATUS  
-------------------------------------------------------------
 3000003       HS00001       4-2021           COMPLETE
 3000003       HS00183       5-2021           COMPLETE
 3000003       HS00776       10-2021          FALSE
 3000003       HS00559       11-2021          COMPLETE
 3000003       HS00221       12-2021          ACTIVE
 3000003       HS00222       1-2022           COMPLETE
--------------------------------------------------------------

when i select 'ORDER_ID' = 3000003, it will output with data following as:

------------------------------
ORDER_ID    |   HS_TIME
------------------------------
3000003           4
------------------------------

this is my recipe:

If i select DATE_PERIOS is: 1/2022, it will display HS_TIME is 4 (3 times COMPLETE before: 4-2021, 5-2021, 11-2021 1)

If i select DATE_PERIOS is: 11/2021, it will display HS_TIME is 3 (2 times COMPLETE before: 4-2021, 5-2021 1)

Note: only 1 when status is COMPLETE

How to count number of Month with condition to output result as above ? Thanks a lot

CodePudding user response：

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.

CodePudding user response：

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>

CodePudding user response：

with cte as(
   select *,row_number()over(partition by ORDER_ID order by ORDER_ID) as seq
   from tb
),
cte2 as(
select *,
       case
         when DATE_PERIOS = '1-2022'
           then (select count(*)   1 from cte t2 where t1.seq > t2.seq and t2.STATUS = 'COMPLETE')
         else 0 end as HS_TIME_1_2022,
        case
         when DATE_PERIOS = '11-2021'
           then (select count(*)   1 from cte t2 where t1.seq > t2.seq and t2.STATUS = 'COMPLETE')
         else 0 end as HS_TIME_11_2022
from cte t1)
select ORDER_ID,max(HS_TIME_1_2022)HS_TIME_1_2022,max(HS_TIME_11_2022)HS_TIME_11_2022
from cte2
group by ORDER_ID

The following output is taken in sql-server but can also be run in Oracle.

Result