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

Expected output

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

T-SQL Query | [ The Multiple Condition Puzzle ]

Puzzle Statement

1. We have a table called “Groups”
2. I need to pick the group_no’s who has group_name both TEST1 and TEST2 and Active should be 1. In this case I need 100 and 300 as this both group_no contains TEST1 and TEST2 and are active.
3. Here i don’t get 200 because it has both groupnames but for the 4th record its active status is 0.
4. Here i dont get 400 because it has only TEST1 but dont have a record with groupname TEST2.
5. Please check out the sample input and expected output for details.

Sample Input

Expected output

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 Groups
(
	 Group_id INT
	,Group_no INT
	,Group_name VARCHAR(10)
	,Active INT
)
GO

INSERT INTO Groups VALUES
(1, 100, 'TEST1', 1),
(2, 100, 'TEST2', 1),
(3, 200, 'TEST1', 1),
(4, 200, 'TEST2', 0),
(5, 300, 'TEST1', 1),
(6, 300, 'TEST2', 1),
(7, 400, 'TEST1', 1)

--

UPDATE – 23-Apr-2015 – Solution 1

--

;WITH CTE1 AS
(
	SELECT Group_no FROM Groups WHERE Active = 1 AND Group_name = 'TEST1'
)
, CTE2 AS
(
	SELECT Group_no FROM Groups WHERE Active = 1 AND Group_name = 'TEST2'
)
SELECT g.* FROM CTE1 a
INNER JOIN CTE2 b ON a.Group_no = b.Group_no
INNER JOIN Groups g on a.Group_no = g.Group_no

--

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

Keep Learning

http://MSBISkills.com