SQLTeam.com | Weblogs | Forums

SQL : Running Total Mins per week

Hi Everyone.

I have this data:

iDate Noofweek Noday dayname Timein Timeout totalmins undertime overtimepm RunningHrs
1/1/2018 1 2 Monday 8:30 8:30 420 60 0 -60
1/2/2018 1 3 Tuesday 8:30 8:30 480 0 0 -60
1/3/2018 1 4 Wednesday 7:30 7:30 540 0 60 0
1/4/2018 1 5 Thursday 7:30 7:30 540 0 60 60
1/5/2018 1 6 Friday 7:30 7:30 460 20 0 -20
1/6/2018 1 7 Saturday 0:00 0:00 0 0 0 0
1/7/2018 2 1 Sunday 0:00 0:00 0 0 0 0
1/8/2018 2 2 Monday 7:30 7:30 465 15 0 -15
1/9/2018 2 3 Tuesday 7:30 7:30 540 0 60 45
1/10/2018 2 4 Wednesday 7:30 7:30 470 10 0 -10
1/11/2018 2 5 Thursday 7:30 7:30 470 10 0 -10
1/12/2018 2 6 Friday 7:30 7:30 540 0 60 40

Above table shows the running balance per week.

And I want to do it in sql and get the same result as shown above with same conditions:

  1. Running balance only apply if there is undertime >0 from Mon-Fri. And if, the running balance becomes ZERO or POSITIVE then reset the running balance.

So far, this is my codes, but i dont know how to reset the running balance.
create table #timeAttendance
(
iDate datetime
,NoOfWeek int
,NoDay int
,DayName varchar(20)
,TimeIn datetime
,Timeout datetime
,totalmins float
,undertime float
,overtimepm float
)
insert into #timeAttendance
VALUES
('1/1/2018', 1, 2, 'Monday' ,'8:30', '16:30',420, 60, 0),
('1/2/2018', 1, 3, 'Tuesday' ,'8:30', '17:30', 480, 0, 0),
('1/3/2018', 1, 4, 'Wednesday' ,'7:30', '17:30',540, 0, 60),
('1/4/2018', 1, 5, 'Thursday' ,'7:30', '17:30',540, 0, 60),
('1/5/2018', 1, 6, 'Friday' ,'7:30', '16:10',460, 20, 0),
('1/6/2018', 1, 7, 'Saturday' ,'0:00', '0:00',0, 0, 0),
('1/7/2018', 2, 1, 'Sunday' ,'0:00', '0:00',0, 0, 0),
('1/8/2018', 2, 2, 'Monday' ,'7:30', '16:15',465, 15, 0),
('1/9/2018', 2, 3, 'Tuesday' ,'7:30', '17:30',540, 0, 60),
('1/10/2018', 2, 4, 'Wednesday' ,'7:30', '16:20',470, 10, 0),
('1/11/2018', 2, 5, 'Thursday' ,'7:30', '16:20', 470, 10, 0),
('1/12/2018', 2, 6, 'Friday' ,'7:30', '17:30', 540, 0, 60)

select
iDate=convert(varchar(10),idate,101)
,Noofweek,Noday,[dayname]
,Timein = convert(varchar(5),timein,108)
,Timeout = convert(varchar(5),timein,108)
,totalmins
,undertime
,overtimepm
,RunningHrs = (select SUM(overtimepm-undertime)
from #timeAttendance
where NoDay <= A.NoDay
AND NoOfWeek = a.NoOfWeek )
from #timeAttendance a

Any help please.

Thanks in advance.