How to use partition by with sum Limitation in Sql Server?

Hassan Tirmizi 0
Viewed 25

This query returns the Running Sum of Late minutes which is working fine but I have a limit of 250 minutes. If the running Sum is more than 250 then it should skip its calculation and it should bemoved to next row till 250 sum completed.

select t.EmployeeIndex, t.AtDate, t.LateMM,
    sum(t.LateMM) over (partition by EmployeeIndex order by EmployeeIndex,AtDate) RunningLateMM
    --Sum(t.LateMM)  RunningLateMM    
from    (
    Select datediff(mi,s.timein, s.empin) LateMM, s.employeeindex,
           s.atdate, ROW_NUMBER() over (partition by s.EmployeeIndex order by s.EmployeeIndex) SNo
    from   tm_summary s
           left outer join tm_relaxation TR on TR.EmployeeIndex= S.employeeindex and tr.atdate=s.atdate
    where  s.atdate between '6-20-2022' and '7-26-2022' and (s.adjlvlc =0.25 or TR.RelaxationIndex is not null )
           and s.IsLeave=0 and s.IsHoliday=0
           and s.employeeindex =259744
) t

Current out is attached in image

Desire result is like this:

enter image description here

sql
0 Answers
Related