Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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