Get latest address column with Flag = Y in all rows

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    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

     

    Thank you in advance for your help!

     

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    something like

    select y.id,y.name1,x.address1,x.newaddressflag,y.currentaddress from ##temp y inner join (select distinct id,address1 from ##temp where newadressflag='y') as x  on y.id=x.id

    MVDBA

  • ZZartin

    SSC-Dedicated

    Points: 30414

    Assuming you'll only have 1 record per ID marked with a Y you could do something like,

    SELECT *, 
    MAX(CASE WHEN NewAddressFlag = 'Y' THEN Address1 ELSE '' END) OVER(PARTITION BY Id) AS CurrentAddress
    FROM ##temp
  • Jeffrey Williams

    SSC Guru

    Points: 88449

    Declare @tempAddress Table (
    Id int
    , Name1 varchar(10)
    , Address1 varchar(20)
    , NewAddressFlag varchar(1)
    , CurrentAddress varchar(20)
    );

    Insert Into @tempAddress(Id, Name1, Address1, NewAddressFlag, CurrentAddress)
    Values (111, 'JOHN', 'Red Street' , 'Y' , Null)
    , (111, 'JOHN', 'Yellow Street' , 'N' , Null)
    , (111, 'JOHN', 'Green Street' , 'N' , Null)
    , (111, 'JOHN', 'Blue Street' , 'N' , Null)
    , (222, 'MARK', 'Sun Street' , 'Y' , Null)
    , (222, 'MARK', 'Beach Street' , 'N' , Null)
    , (222, 'MARK', 'State Street' , 'N' , Null)
    , (222, 'MARK', 'Main Street' , 'N' , Null);

    Select *
    , CurrentAddress = first_value(ta.Address1) over(Partition By Id Order By ta.NewAddressFlag desc)
    From @tempAddress ta;

     

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • drew.allen

    SSC Guru

    Points: 76721

    Jeffrey Williams wrote:

    Declare @tempAddress Table (
    Id int
    , Name1 varchar(10)
    , Address1 varchar(20)
    , NewAddressFlag varchar(1)
    , CurrentAddress varchar(20)
    );

    Insert Into @tempAddress(Id, Name1, Address1, NewAddressFlag, CurrentAddress)
    Values (111, 'JOHN', 'Red Street' , 'Y' , Null)
    , (111, 'JOHN', 'Yellow Street' , 'N' , Null)
    , (111, 'JOHN', 'Green Street' , 'N' , Null)
    , (111, 'JOHN', 'Blue Street' , 'N' , Null)
    , (222, 'MARK', 'Sun Street' , 'Y' , Null)
    , (222, 'MARK', 'Beach Street' , 'N' , Null)
    , (222, 'MARK', 'State Street' , 'N' , Null)
    , (222, 'MARK', 'Main Street' , 'N' , Null);

    Select *
    , CurrentAddress = first_value(ta.Address1) over(Partition By Id Order By ta.NewAddressFlag desc)
    From @tempAddress ta;

    You should ALWAYS specify the frame (when applicable) instead of using the default, because the default uses RANGE, but ROWS will always perform better than RANGE.  Below are the IO stats with the default frame and specifying ROWS BETWEEN UNBOUNDED PRECEDING.

    /*  Default Frame  */
    Table 'Worktable'. Scan count 6, logical reads 41, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#A8267936'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    /* ROWS BETWEEN UNBOUNDED PRECEDING */
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#A8267936'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Notice that the first worktable has 6 scans and 41 logical reads, whereas the second has 0 scans and 0 logical reads.  Both queries have 1 scan and 1 logical read of the temp table.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    Thank you everyone for your help!

    it works!

     

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

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

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