Case Statement Subquery to Set a bit value

  • 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.

  • 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
  • ahh....worked well.

    Thanks Luis.

    Keep on Keeping on and enjoy your weekend!

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

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