Tags

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


T-SQL Query | [ The Grouping Puzzle ]

Puzzle Statement

The Puzzle is self explanatory. Please check out the sample input and expected output for details.

Sample Input

day leva levb
18 A B
19 A B
20 B A
21 A B
22 A B
23 A B

Expected Output

FROM TO Leva Levb
18 19 A B
20 20 B A
21 23 A B

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • 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.

—


declare @tbl as table(day int,leva varchar(40),levb varchar(40))
insert into @tbl
select 18 ,'A' ,'B' union all
select 19, 'A', 'B' union all
select 20, 'B', 'A' union all
select 21, 'A', 'B' union all
select 22 ,'A' ,'B' union all
select 23, 'A', 'B'

—

Update May 6 | Solution 1


--

SELECT MIN(day) [FROM] , MAX(day) [TO] , MIN(Leva) Leva , MAX(Levb) Levb
FROM
(
     SELECT * , DAY - ROW_NUMBER() OVER (PARTITION BY leva,levb ORDER BY %%Physloc%%) rnk FROM @tbl
) A
GROUP BY rnk
ORDER BY [FROM]

--

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

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal