Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ The Complex Time Puzzle ]
- We have a table called “Flag”
- We need two columns in the output i.e. Time1 and Time2
- 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
- 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)
- For each Id = 1, if the next row is not ID = 2 then show value ‘0:00:00’
- Same if for Id = 2 if the previous value is not id = 1 then show value ‘0:00:00’
- Please check out the sample input and expected output for details.
- Your solution should be should use “SELECT” statement or “CTE”
- Your solution should be generic in nature.
- Add your solution(s) in the comments section or send you solution(s) to email@example.com
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.