CODEWITHSUNDEEP
sqlsql-serversum
Steve C
Steve C

Reputation: 11

SQL query sum column till a row condition met and continue sum column

Data:

enter image description here

SQL Server 2012 - I need to "scan" through a table as shown above and sum up the duration column until the row having "BREAK" in Paycode column.

Then continue to sum up duration column after the "BREAK" row

i.e. i want to get results of

Sum of Duration
---------------
6.08
3.33

I tried this statement, but it's not returning the above desired result:

SELECT duration, paycode, visitstart
INTO #client
FROM VISITS
WHERE visitstart > '2020-01-20'
  AND visitstart < '2020-01-21'
  AND paycode <> 'BREAK'
ORDER BY VISITSTART, CLIENT_ID

SELECT SUM(duration) AS total_duration, visitstart, paycode
FROM #client
GROUP BY visitstart, paycode

DROP TABLE #client

Please kindly advise

Upvotes: 1

Views: 1166

Answers (3)

Olga Romantsova
Olga Romantsova

Reputation: 1081

So,Steve C, I have tried to help you... I have made several cte

  1. cte (cteBreak) - you are just select only breaks.
  2. cte (breaks_chains) - numbering each break (because can't use row_numbing)
  3. breaks_chains- get breaks intervals by pair (eg time first break - time second break, time second break-time third break)

Note: if it is last interval then column end_break is replacing by getdate(), just to close interval

  1. and at last we combine our table with breaks_chains Instead of Table type yours Table name

    --just get rows with BREAK paycode
 with cteBreak as
  (
    select * from Table where paycode='BREAK'
  ),

 --numbering breaks (=row_numbering() in later version sql server)
 breaks_chains as
 (
  select cte1.visitstart visit_st,count(cte2.visitstart)+1 Num from cteBreak cte1
  left join cteBreak cte2
  on cte1.visitstart>cte2.visitstart
  group by cte1.visitstart
 ),

 --get pairs as "start_break - end_break"
 break_interval as
 (
  select t1.visit_st begin_break, COALESCE(t2.visit_st,getdate()) as end_break,t1.Num from 
  breaks_chains t1
  left join breaks_chains t2
  on t2.Num-t1.Num=1
 )


 select sum(duration) from
 (
 select duration,paycode,
  case
     when 
        (Table.visitstart>break_interval.begin_break
         and Table.visitstart<break_interval.end_break)
     then break_interval.end_break    

     when (Table.visitstart<break_interval.begin_break and Num=1)
     then  break_interval.begin_break
 end NumGroup
 from Table
 left join break_interval
 on 
   (Table.visitstart>break_interval.begin_break  
   and Table.visitstart<break_interval.end_break)
 or 
   (Table.visitstart<break_interval.begin_break and Num=1) --for first sequence (before first break)
 )t
 where paycode<>'BREAK'
 group by NumGroup

P.S. If you have questions, write it in comments please)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can assign groups by summing the number of times that "BREAK" appears. Then you can aggregate. I am not quite sure how the final durations are defined -- in particular whether the "BREAK" row is included. Assuming that it is:

select min(visitstart), max(visitstart),
       sum(duration)
from (select t.*,
             sum(case when paycode = 'BREAK' then 1 else 0 end) over (order by visitstart) as grp
      from t
     ) t
where paycode <> 'BREAK'
group by grp

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Try the below one - it's a gap & island problem

select sum(duration) as total_duration
from
(
select t.*,
       row_number() over (order by visitstart) as seqnum,
       row_number() over (partition by case when paycode='Break' then 1 else 0 end order by visitstart) as seqnumt
from yourtablename t
)A 
where paycode<>'Break'
group by (seqnum-seqnumt)

Upvotes: 2

Related Questions