T-SQL Query | [ The ZIP Grouping Puzzle ]

Puzzle Statement

1. We have a table called zip33
2. We have group data based on code and then Zip Start & Zip End if Zip Start < Zip End
3. Please check out the sample input and expected output for details.

Sample Input

 zip code 56790000 A101 56800000 A101 56810000 A101 56820000 A101 56890000 A101 56900000 A102 56901111 A102 56901168 A103 56910000 A102 56920000 A102

Expected Output

 Code ZipStart ZipEnd A101 56790000 56890000 A102 56900000 56901111 A102 56910000 56920000 A103 56901168 56901168

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.

 — ``` create table zip33 (zip varchar(9), code varchar(4)) insert zip33 select '056790000' ,'A101' insert zip33 select '056800000' ,'A101' insert zip33 select '056810000' ,'A101' insert zip33 select '056820000' ,'A101' insert zip33 select '056890000' ,'A101' insert zip33 select '056900000' ,'A102' insert zip33 select '056901111' ,'A102' insert zip33 select '056901168' ,'A103' insert zip33 select '056910000' ,'A102' insert zip33 select '056920000' ,'A102' ``` —

Update May 6 | Solution 1

 ``` -- ;WITH CTE AS ( SELECT * , ROW_NUMBER() OVER (ORDER BY %%Physloc%%) - DENSE_RANK() OVER ( PARTITION BY code ORDER BY zip) rnk FROM zip33 ) SELECT MIN(Code) Code , MIN(zip) ZipStart , MAX(zip) ZipEnd FROM CTE GROUP BY rnk -- ```

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

Keep Learning

http://MSBISkills.com