November 19, 2015 at 12:32 pm
Hello SQL Friends,
How can I set a value for a field based on transactions in another table?
I am running query where I need to set a bit value of 1 if the record in the parent query has a specific transaction value on a different table.
Below is tempdb code you can test against.
use tempdb
go
drop table #items
go
drop table #itemtrans
go
create table #items
(
Itemvarchar (10) not null
,MFRvarchar (30) not null
)
create table #transtable
(
Itemvarchar (10) not null
,Vendor varchar (10) not null
,Datedatetime not null
,costmoneynot null
)
insert into #items
(
Item,MFR
)
Values
('A01','CHC')
,('B02','Cableco')
,('C03','FittingCorp')
,('D04','MMO')
,('E05','MMO')
,('F06','GenTech')
,('G07','Ross')
,('H08','Generate')
,('I09','Zulu')
insert into #transtable
(
Item,vendor,Date,Cost
)
Values
('A01','1175','11/19/2015','15')
,('A01','1174','11/18/2015','14')
,('A01','2000','11/17/2015','14.50')
,('B02','1311','11/17/2015','25')
,('B02','3000','11/19/2015','27')
,('B02','2500','11/18/2015','28')
,('C03','2500','11/18/2015','30')
,('C03','1918','11/17/2015','20')
,('D04','1505','11/17/2015','10')
,('D04','2000','11/18/2015','12')
,('E05','2000','11/18/2015','15')
,('F06','3000','11/18/2015','50')
,('G07','2500','11/18/2015','20')
,('H08','1175','11/19/2015','15')
,('I09','1174','11/17/2015','14')
select * from #items
select * from #transtable
select Item
,(Case
When Exists(Select Item from #transtable
where Vendor in('1174','1311','1505'))
Then '1'
Else '0'
End
) as LandedCost
From #items
The desired result should show as below.
Notice Only A01, B02, D04, I09 have the 1 set against it.
My current issue is all items have a landed cost value of 1.
ItemLandedCost
A011
B021
C030
D041
E050
F060
G070
H080
I091
Any help you can provide would be appreciated. Thanks in advanced.
November 19, 2015 at 1:01 pm
You missed the relationship between the inner and the outer query.
select Item
,(Case
When Exists(SELECT t.Item
FROM #transtable t
WHERE t.Vendor in('1174','1311','1505')
AND t.Item = i.Item)
Then '1'
Else '0'
End
) as LandedCost
From #items i
I would also change the query merely for esthetic reasons.
SELECT Item
,LandedCost
FROM #items i
CROSS APPLY (SELECT CASE WHEN EXISTS(SELECT t.Item
FROM #transtable t
WHERE t.Vendor in('1174','1311','1505')
AND t.Item = i.Item)
THEN '1'
ELSE '0'
END AS LandedCost) x
November 20, 2015 at 10:47 am
ahh....worked well.
Thanks Luis.
Keep on Keeping on and enjoy your weekend!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy