Hello All
I have an issue regarding getting column value with flag=Y in all rows- as latest address.
Here is sample code - I was trying to do it without using a CTE/temp table
Is that possible in simple SQL query without using a cte?
Currently the output gives Latest Address in only row where Flag=Y but i want that Address in All rows.
Please advice.
drop table ##temp
Create table ##temp
(
Id int,
Name1 varchar(10),
Address1 varchar(20),
NewAddressFlag varchar(1),
CurrentAddress varchar(20)
)
Insert into ##temp
SELECT 111, 'JOHN', 'Red Street' , 'Y' , NULL UNION
SELECT 111, 'JOHN', 'Yellow Street' , 'N' , NULL UNION
SELECT 111, 'JOHN', 'Green Street' , 'N' , NULL UNION
SELECT 111, 'JOHN', 'Blue Street' , 'N' , NULL UNION
SELECT 222, 'MARK', 'Sun Street' , 'Y' , NULL UNION
SELECT 222, 'MARK', 'Beach Street' , 'N' , NULL UNION
SELECT 222, 'MARK', 'State Street' , 'N' , NULL UNION
SELECT 222, 'MARK', 'Main Street' , 'N' , NULL
------------------------------------
Select * from ##temp
--------Query to Derive Current address column----------------------------
Select * ,CASE WHEN NewAddressFlag = 'Y' THEN Address1 END as CurrentAddressDerived
from ##temp
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]