SQL Puzzle | The Address puzzle

In this puzzle you have to create a new columns using select, When the value of the AddressType column is cor then you have to take the minimum or the first value of the address line else you show the same value from the address line column.
Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE ADDR
(
	 custid   INT 
	,addresstype  VARCHAR(10) 
	,adressline1  VARCHAR(20) 
)
GO

INSERT INTO ADDR VALUES 
(1,        'cor'           ,'london'	  ),
(1,        'cor'           ,'london'	  ),
(1,        'res'           ,'manchester'  ),
(1,        'res'           ,'luton'		  )
GO

INSERT INTO ADDR VALUES 
(  1, 'cor' , 'Athens' )
GO

--

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

Solution – 1

--


select * , CASE WHEN addresstype = 'COR' THEN FIRST_VALUE(adressline1) OVER (PARTITION BY custid,addresstype ORDER BY adressline1)  ELSE 
 adressline1 end as address1  from ADDR

--

Solution – 2

--

select * , CASE WHEN addresstype = 'COR' THEN MIN(adressline1) OVER (PARTITION BY custid,addresstype)   else adressline1
end as address1  from ADDR

--

Output

/*------------------------

select * , CASE WHEN addresstype = 'COR' THEN FIRST_VALUE(adressline1) OVER (PARTITION BY custid,addresstype ORDER BY adressline1)  ELSE 
 adressline1 end as address1  from ADDR
------------------------*/
custid      addresstype adressline1          address1
----------- ----------- -------------------- --------------------
1           cor         Athens               Athens
1           cor         london               Athens
1           cor         london               Athens
1           res         luton                luton
1           res         manchester           manchester

(5 row(s) affected)

--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com