I want to count the number of days, how many days remained active on an offer.

That is my Tbale:

CREATE TABLE myTable(u_id, , offer, status,status_date) as
SELECT 1,  'Offer_1', 'Active', TO_DATE('2021/12/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss')
  FROM dual
UNION ALL
SELECT 1, 'Offer_1', 'Deactive', TO_DATE('2022/02/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss') FROM dual
UNION ALL
SELECT 1, 'Offer_1','Active', TO_DATE('2022/03/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss')  FROM dual
UNION ALL
SELECT 1, 'Offer_1','Deactive' TO_DATE('2022/04/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss')  FROM dual
UNION ALL
SELECT 1, 'Offer_2','Active', TO_DATE('2022/12/30 21:02:44', 'yyyy/mm/dd hh24:mi:ss') FROM dual

That is my script:

select distinct u_id,offer_id,
trunc(nvl(case when status = 'Deactive' then status_date end),sysdate) - 
trunc(case when status = 'Active' then status_date end) date_diff 
from myTable

but it gives me wrong result.

expected output:

CodePudding user response：

You can do it as follows :

select u_id, offer, sum(case when status = 'Active' then ceil(trunc(sysdate) - status_date) else ceil(status_date - trunc(sysdate))  end) as total_day
from myTable
group by u_id, offer

Result :

U_ID    OFFER   TOTAL_DAY
1       Offer_1  460
1       Offer_2  31

ceil used to round the specified number up (number of days in this case)

demo here

CodePudding user response：

Provided 'Active' and 'Deactive' are always alternating, you can use LEAD to get the desctive date for each active date. At last aggregate and sum.

select
  u_id,
  offer,
  trunc(sum(cast(next_status_date as date) - cast(status_date as date))) as total_days
from
(
  select
    u_id, offer, status, status_date,
    coalesce
    (
      lead(status_date) over (partition by u_id, offer order by status_date),
      sysdate
    ) as next_status_date
  from mytable
)
where status = 'Active'
group by u_id, offer
order by u_id, offer;

Demo: https://dbfiddle.uk/YDVTqEiR