# 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 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

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.

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