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”.

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