T-SQL Query | [ The Complex Time Puzzle ]

Puzzle Statement

1. We have a table called “Flag”
2. We need two columns in the output i.e. Time1 and Time2
3. There is a sequence of Time in Tim column with ID = 1 and ID = 2. Conside ID = 1 as the start time and ID = 2 as the End Time
4. In the Time1 column we have to pick value from Tim column where ID = 1 and the in the Time2 column we have to pick value from Tim column where ID = 2 (Immediate next Id)
5. For each Id = 1, if the next row is not ID = 2 then show value ‘0:00:00’
6. Same if for Id = 2 if the previous value is not id = 1 then show value ‘0:00:00’
7. Please check out the sample input and expected output for details.

Sample Input

 ID Tim 1 09:05:00 2 09:10:00 1 10:00:00 2 10:15:00 1 11:00:00 1 11:30:00 2 11:45:00 2 11:50:00

Expected output

 Time1 Time2 09:05:00 09:10:00 10:00:00 10:15:00 11:00:00 00:00:00 11:30:00 11:45:00 00:00:00 11:50:00

Rules/Restrictions

• Your solution should be should use “SELECT” statement or “CTE
• Your solution should be generic in nature.

Script

Use the below script to generate the source table and fill them up with the sample data.

 ``` -- CREATE TABLE Flag ( ID INT ,Tim VARCHAR(8) ) GO INSERT INTO Flag(ID,Tim) VALUES (1,'9:05:00'), (2,'9:10:00'), (1,'10:00:00'), (2,'10:15:00'), (1,'11:00:00'), (1,'11:30:00'), (2,'11:45:00'), (2,'11:50:00') -- ```

UPDATE – 23-Apr-2015 – Solution 1

 ``` -- ;WITH CTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - ID rnk , * FROM Flag ) ,CTE1 AS ( SELECT MIN(Tim) Time1, MAX(Tim) Time2 FROM CTE GROUP BY rnk ) SELECT CASE WHEN Time1 = Time2 AND ID = 2 THEN '0:00:00' ELSE Time1 END Time1 ,CASE WHEN Time1 = Time2 AND ID = 1 THEN '0:00:00' ELSE Time2 END Time2 FROM CTE1 a INNER JOIN Flag b ON a.Time1 = b.Tim -- ```

UPDATE – 28-Apr-2015 – Solution 2

 ``` --Posted By Isha Mattoo ;With cte AS ( Select *,ISnull(lag(ID) OVER (ORDER BY (select null)),0) As Prev_ID ,ISnull(Lead(ID) OVER (ORDER BY (select null)),0) As Next_ID from Flag ) ,cte2 as( select * , case when id = 1 and (next_id = 2 or Prev_id =2 or next_id = 0) then Tim when id = 2 and Prev_ID = 2 then '00:00:00' end as Tim1 , CASE WHEN id = 2 and (Prev_id = 1 or next_id = 1 or next_id = 0) then Tim when id = 1 and Next_ID = 1 then '00:00:00' end as Tim2 from cte ) ,CTE21 AS ( SELECT Tim1 , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk from cte2 WHERE Tim1 IS NOT NULL ) ,CTE22 AS ( SELECT Tim2 , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk from cte2 WHERE Tim2 IS NOT NULL ) SELECT Tim1,Tim2 FROM CTE22 c INNER JOIN CTE21 c1 on c.rnk = c1.rnk -- ```

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

Keep Learning

http://MSBISkills.com