This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to prepare the report on the Start time and End time basis on Corresponded IDs. If corresponded ID is not available, set 00:00:00 for the time value. (E.g, Corresponded IDs are 1-2)
Input Data:
|
1 2 3 4 5 6 7 8 9 |
ID Times ----------- -------- 1 08:00:00 1 16:16:00 1 12:00:00 2 13:08:00 1 08:16:00 2 10:00:00 2 16:08:00 |
Expected Output:
|
1 2 3 4 5 6 7 |
StartTime EndTime --------- -------- 08:00:00 0:00:00 16:16:00 0:00:00 12:00:00 13:08:00 08:16:00 10:00:00 0:00:00 16:08:00 |
Create a table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE CorrespondTimes ( ID INT ,Times CHAR(8) ) GO INSERT INTO CorrespondTimes VALUES (1,'08:00:00') ,(1,'16:16:00') ,(1,'12:00:00') ,(2,'13:08:00') ,(1,'08:16:00') ,(2,'10:00:00') ,(2,'16:08:00') GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
;WITH CTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - ID rnk, * FROM CorrespondTimes ) ,CTE1 AS ( SELECT MIN(Times) StartTime, MAX(Times) EndTime FROM CTE GROUP BY rnk ) SELECT CASE WHEN StartTime = EndTime AND ID = 2 THEN '0:00:00' ELSE StartTime END StartTime ,CASE WHEN StartTime = EndTime AND ID = 1 THEN '0:00:00' ELSE EndTime END EndTime FROM CTE1 a INNER JOIN CorrespondTimes b ON a.StartTime = b.Times |
Please try the different solution for this puzzle and share it via comment...
Leave a Reply