SQL Puzzle | The Late Tolerance Puzzle

IN this puzzle you have to update 3 columns named LateInWork,LateInAfterTolerance,LeaveStatusId. It is based on how much you are late to the company. Company gives you 15 minutes grace period.

1. LateInWork – if you are late came after the grace then value in this column will be 1 else 0.
2. LateInAfterTolerance – if you came after the grace then this column will have difference in time like how much you are late else 00:00.
3. LeaveStatusId – if the difference between 0-59 then L1, if more than >= 60 then L2 , if the time is <=0 then make it blank.

Please check out the sample input values and sample expected output below.

Sample Input

ID Latein LateInAfterTolerance LateInWork LeaveStatusId
1 00:15:00.0000000 NULL NULL NULL
2 00:00:00.0000000 NULL NULL NULL
3 NULL NULL NULL NULL
4 00:14:00.0000000 NULL NULL NULL
5 00:16:00.0000000 NULL NULL NULL
6 01:14:00.0000000 NULL NULL NULL
7 01:20:00.0000000 NULL NULL NULL

Expected Output

ID Latein LateInAfterTolerance LateInWork LeaveStatusId
1 00:15:00.0000000 00:00:00.0000000 0
2 00:00:00.0000000 00:00:00.0000000 0
3 NULL 00:00:00.0000000 0
4 00:14:00.0000000 00:00:00.0000000 0
5 00:16:00.0000000 00:01:00.0000000 1 L0
6 01:14:00.0000000 00:59:00.0000000 1 L0
7 01:20:00.0000000 01:05:00.0000000 1 L1

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE THATTENDANCE ( ID INT ,Latein TIME ,LateInAfterTolerance TIME NULL ,LateInWork TINYINT NULL ,LeaveStatusId VARCHAR(3) NULL ) GO INSERT INTO THATTENDANCE VALUES (1,'00:15', NULL , NULL , NULL ), (2,'00:00', NULL , NULL , NULL ), (3,NULL , NULL , NULL , NULL), (4,'00:14' , NULL , NULL , NULL), (5,'00:16', NULL , NULL , NULL ), (6,'01:14' , NULL , NULL , NULL), (7,'01:20' , NULL , NULL , NULL ) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution 1

 ``` -- DECLARE @Grace TIME = '00:15'; UPDATE T SET T.LateInWork = CASE WHEN LateIn < @Grace THEN 0 ELSE 1 END , T.LateInAfterTolerance = CASE WHEN DATEDIFF(n, @Grace, LateIn) < 0 THEN '00:00' ELSE CAST(DATEADD(n, DATEDIFF(n, @Grace, LateIn), 0) AS TIME(0)) END , T.LeaveStatusId = CASE WHEN DATEDIFF(n, @Grace, LateIn) <= 0 THEN '' WHEN DATEDIFF(n, @Grace, LateIn) BETWEEN 0 AND 59 THEN 'L0' ELSE 'L1' END FROM THATTENDANCE T -- ```

Solution 2

 ``` -- DECLARE @Tolerance AS TIME = '00:15:00' DECLARE @Mins AS INT = 0 SELECT @Mins = CAST( SUBSTRING(CAST(@Tolerance AS VARCHAR(20)), 1, CHARINDEX(':',@Tolerance,0) -1 ) AS INT ) * 60 + CAST( SUBSTRING(CAST(@Tolerance AS VARCHAR(20)), CHARINDEX(':',@Tolerance,0) + 1 , 2 ) AS INT ) ;WITH CTE AS ( SELECT *, ISNULL( CAST( SUBSTRING(CAST(Latein AS VARCHAR(20)), 1, CHARINDEX(':',LateIn,0) -1 ) AS INT ) * 60 + CAST( SUBSTRING(CAST(Latein AS VARCHAR(20)), CHARINDEX(':',LateIn,0) + 1 , 2 ) AS INT ) , 0) Mins FROM THATTENDANCE AS A ) UPDATE CTE SET LateInWork = CASE WHEN Mins <= @Mins THEN 0 WHEN Mins > @Mins THEN 1 END , LeaveStatusId = CASE WHEN Mins <= @Mins THEN '' WHEN Mins > @Mins AND Mins < 75 THEN 'L0' WHEN Mins >= 75 THEN 'L1' END ,LateInAfterTolerance = CONCAT( RIGHT(CONCAT('0',CASE WHEN @Mins < Mins THEN Mins - @Mins ELSE 0 END / 60),2) , ':' , RIGHT(CONCAT('0',CASE WHEN @Mins < Mins THEN Mins - @Mins ELSE 0 END % 60),2) ) FROM CTE -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com