CODEWITHSUNDEEP
sqlsql-server
sherifffruitfly
sherifffruitfly

Reputation: 465

SQL Server state change query via window functions: How to get running total time in state *WITHIN* state blocks?

I'm tracking machines and the amount of time they spend in various states with the following query:

    select ks.CalendarId 
    , ks.machineid
    , ks.machinestateid
    , lead(ks.machinestateid) over (partition by ks.machineid, ks.calendarid order by ks.machinefmstatetime desc) prevstateid
    , ks.machinestatehistoryid
    , ks.machinefmstatetime
    , ks.StateTimeInSeconds
    , sum(ks.statetimeinseconds) over (partition by ks.machineid, ks.calendarid, ks.machinestateid order by ks.machinefmstatetime) StateTimeSecTotal
from [dbo].[MachineStateDailyDistributionFact] ks
where calendarid in (20180425)
    and ks.machineid in (38141)
order by ks.MachineFmStateTime

This is all good except for StateTimeSecTotal. When a machine goes back into a state that it was in sometime prior, the StateTimeSecTotal picks up where it left off. I need the second count to "start over".

For example in the below pic, I need the highlighted cell to be 0, and then proceed to count up as it already is.

enter image description here

How do I change my query to have total state duration "start over" when in a different "state block of time"?

thanks for any tips, sff

Upvotes: 1

Views: 135

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You have a gaps-and-islands problem. Here is a solution using row_number():

select . . .,
       sum(ks.statetimeinseconds) over (partition by ks.machineid, ks.calendarid, ks.machinestateid order by ks.machinefmstatetime) StateTimeSecTotal
select . . .,
       sum(ks.statetimeinseconds) over (partition by ks.machineid, ks.calendarid, ks.machinestateid, seqnum_1-seqnum_2 order by ks.machinefmstatetime) StateTimeSecTotal

from (select ks.*,
             row_number() over (partition by ks.machineid, ks.calendarid order by ks.machinefmstatetime) as seqnum_1,
             row_number() over (partition by ks.machineid, ks.calendarid, ks.machinestateid order by ks.machinefmstatetime) as seqnum_2
      from [dbo].[MachineStateDailyDistributionFact] ks
      where calendarid in (20180425) and ks.machineid in (38141)
     ) ks
order by ks.MachineFmStateTime;

The difference of row numbers identifies the adjacent groups of machine id states.

Upvotes: 1

Related Questions