-1

I currently need to make a query that creates a running total for a user table depending on a specific status and resets once their status changes. I've done the usual running total script but my problem lies on that I'm unsure how to make the count start from 1 again once they go back to that specific status.
This is the table that I currently have right now:

User Status Date Status_desc
User 1 Working 03-01-2023 1
User 1 Working 02-01-2023 1
User 1 Absent 01-01-2023 0
User 1 Working 12-01-2022 1
User 1 Working 11-01-2022 1
User 1 Working 11-01-2022 1
User 2 Working 03-01-2023 1
User 2 Absent 02-01-2023 0
User 2 Absent 01-01-2023 0
User 2 Working 12-01-2022 1
User 2 Working 11-01-2022 1
User 2 Working 11-01-2022 1

This are the results that I'm trying to get for the running total:

User Status Date Status_desc Running_total
User 1 Working 03-01-2023 1 2
User 1 Working 02-01-2023 1 1
User 1 Absent 01-01-2023 0 0
User 1 Working 12-01-2022 1 3
User 1 Working 11-01-2022 1 2
User 1 Working 11-01-2022 1 1
User 2 Working 03-01-2023 1 1
User 2 Absent 02-01-2023 0 0
User 2 Absent 01-01-2023 0 0
User 2 Working 12-01-2022 1 3
User 2 Working 11-01-2022 1 2
User 2 Working 11-01-2022 1 1

I've tried the normal running total window function and it didn't work since my partition was the status. Is there any workaround for this?

select *
    , sum(t0.working_flag)
    OVER (PARTITION BY (t0.status_desc)
         ORDER BY t0.date asc)
from (select *
    , case when status_desc = 'working' then '1'
    else 0
    end as working_flag
from table) t0
order by date desc
Pat Basilio
  • 1
  • 1
  • 1
    Please do take a moment to look at the preview when posting; you would have been able to spot that your tables did not render and could fix it prior to posting. – Thom A May 08 '23 at 12:24
  • 1
    I've removed the conflicting RDBMS tags here. Please [edit] your question to re tag the one you are *really" using and only that youm adding your attemp(s) to the question and an explanation of why they aren't working would also be beneficial. – Thom A May 08 '23 at 12:25
  • Are working and absent the only status options? – P.Salmon May 08 '23 at 12:42

1 Answers1

0
with data as (
    select *.
        count(case when Status = 'Absent' then 1 end) over (partition by User order by Date) as grp
    from T
)
select *,
    count(case when Status <> 'Absent' then 1 end) over (partition by User, grp order by Date) as RunningTotal
from data
order by User, Date desc;
shawnt00
  • 16,443
  • 3
  • 17
  • 22