Tags

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


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.

PuzzleImageSample 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.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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