Query to get latest address column with Flag = Y in all rows

  • 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]

  • select y.id,y.name1,x.address1,y.newaddressflag,y.current address from ##temp y inner join (select distinct id,address1 from #temp where newaddressflag='Y') x on x.id=y.id

    MVDBA

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply