Case Statement Subquery to Set a bit value

Question

Post reply

Case Statement Subquery to Set a bit value

kwoznica

SSCertifiable

Points: 5275
More actions
November 19, 2015 at 12:32 pm

#302433

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.

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kwoznica SSCertifiable Points: 5275 More actions · Answer 2

ahh....worked well.

Thanks Luis.

Keep on Keeping on and enjoy your weekend!