Asked by:
Please help with an sql to adjust the 10 min clock time

Question
-
I have the following table. I have to get an sql to adjust 10 mins ClockIn and Clockout
If an Employee come in 10 mintus or more than 10 mintus early than EntitledIntime then the clockintime should be considered as NewClockIN = ClockIn + 10 Minitus ie 10 minitus should be Adddfrom actuall clock in
simillarly If an Employee left after 10 mintus or more than 10 mintus late than Entitledouttime then the clockouttime should be considered as NewClockOut = ClockOut - 10 Minitus ie 10 minitus should be deducted from actuall clock Out
If an Employee come in come in early but less than 10 minitus than EntitledIntime then the clockintime should be considered as NewClockIN = EntitledIntime
simillarly If an Employee left after late less than 10 minitus than Entitledouttime then the clockouttime should be considered as NewClockOut = EnttiledoutTime
for example if clockin 8.50 or 8.55 but entitled hrs 9 , then New clockin should be 9, if clockin 8.45 or above but entitled hrs 9 , then New clockin should be Clockin + 10
if Clockout 17.10 or 17.05 But entitled clockout 17, then newclockout = 17.00 , if clockout 17.15 or above then NewClockout = Clockout - 10
create table tblClockHistory (ClockedDate datetime ,MinClockIn decimal ,MaxClokout decimal ,EntIntime decimal ,EntOutTime decimal)
I have the following Record
MinClockIN MaxClokout EntIn Entout ClockedDay 539.00 1041.00 540.00 1020.00 2019-10-01 537.00 1028.00 540.00 1020.00 2019-10-02 540.00 1038.00 540.00 1020.00 2019-10-03 528.00 1031.00 540.00 1020.00 2019-10-04 818.00 1029.00 0.00 0.00 2019-10-07 543.00 1034.00 540.00 1020.00 2019-10-08 544.00 1041.00 540.00 1020.00 2019-10-09 543.00 1030.00 540.00 1020.00 2019-10-10 540.00 793.00 0.00 0.00 2019-10-14 538.00 780.00 540.00 1020.00 2019-10-15 540.00 1025.00 540.00 1020.00 2019-10-16 545.00 1027.00 540.00 1020.00 2019-10-17 540.00 1038.00 540.00 1020.00 2019-10-18 541.00 784.00 540.00 1020.00 2019-10-22 538.00 1029.00 540.00 1020.00 2019-10-23 537.00 1028.00 540.00 1020.00 2019-10-24 537.00 1023.00 540.00 1020.00 2019-10-25 537.00 1029.00 540.00 1020.00 2019-10-29 540.00 1024.00 540.00 1020.00 2019-10-31 536.00 1026.00 540.00 1020.00 2019-11-01 540.00 1027.00 540.00 1020.00 2019-11-05
polachan
- Edited by polachan Wednesday, December 11, 2019 12:04 PM Edit
Wednesday, December 11, 2019 12:03 PM
All replies
-
SELECT *, CASE WHEN EntIntime - MinClockIn > 10 THEN MinClockIn + 10 WHEN EntIntime - MinClockIn > 0 AND EntIntime - MinClockIn <= 10 THEN EntIntime ELSE MinClockIn END AS NewClockIN, CASE WHEN MaxClokout - EntOutTime > 10 THEN MaxClokout - 10 WHEN MaxClokout - EntOutTime > 0 AND MaxClokout - EntOutTime <= 10 THEN EntOutTime ELSE MaxClokout END AS NewClockOut FROM tblClockHistory;
A Fan of SSIS, SSRS and SSAS
- Proposed as answer by Rachel_WangMicrosoft contingent staff Tuesday, December 17, 2019 8:53 AM
Wednesday, December 11, 2019 8:24 PM -
Hi polachan,
Thank you for your issue .
Per your description, I might understand your requirement . I'll confirm some questions with you. If I have any incorrect understanding , please let me know.
1. EntIntime < MinClockIn-->It means if clockin 9.05 or 9.55 but entitled hrs 9 and then how to deal with NewClockIN ? Is this NewClockIN = MinClockIn? Or NewClockIN = MinClockIn-10
2. EntOutTime< MaxClokout-->It means if Clockout 16.55 or 16.05 But entitled clockout 17 and then how to deal with NewClockIN ? Is this NewClockIN = MaxClokout? Or NewClockIN = MaxClokout+10
If NewClockIN = MinClockIn and NewClockIN = MaxClokout, Please try Guoxiong's script. If not please do a little change .
SELECT *, CASE WHEN EntIntime - MinClockIn > 10 THEN MinClockIn + 10 WHEN EntIntime - MinClockIn > 0 AND EntIntime - MinClockIn <= 10 THEN EntIntime ELSE MinClockIn-10 END AS NewClockIN, CASE WHEN MaxClokout - EntOutTime > 10 THEN MaxClokout - 10 WHEN MaxClokout - EntOutTime > 0 AND MaxClokout - EntOutTime <= 10 THEN EntOutTime ELSE MaxClokout+10 END AS NewClockOut FROM tblClockHistory
Could you please explain more ?
Best Regards,
Rachel
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.- Proposed as answer by Rachel_WangMicrosoft contingent staff Tuesday, December 17, 2019 8:53 AM
Thursday, December 12, 2019 6:18 AM -
Hi ,
I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Best Regards,
Rachel
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.Wednesday, December 25, 2019 5:38 AM -
Hi,
If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.
Best Regards,
Rachel
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.Wednesday, January 1, 2020 6:43 AM