November 5, 2019 at 1:50 pm
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!
Thanks [/font]
November 5, 2019 at 2:30 pm
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
November 5, 2019 at 2:37 pm
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
November 5, 2019 at 6:30 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 5, 2019 at 7:07 pm
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
November 6, 2019 at 6:56 pm
Thank you everyone for your help!
it works!
Thanks [/font]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply