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.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 19, 2015 at 1:01 pm

  #1841155

  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

  November 20, 2015 at 10:47 am

  #1841415

  ahh....worked well.

  Thanks Luis.

  Keep on Keeping on and enjoy your weekend!