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:
- 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.