locked
Please help with an sql to adjust the 10 min clock time RRS feed

  • 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

    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