Tags

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


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