CODEWITHSUNDEEP
sqlsql-serverselectcase
SiP
SiP

Reputation: 7

SELECT using a case statement

Good Afternoon

I am new to SQL Server an strugling to build up my select. I was looking for some advice as I dont know where to turn.

So I have a table that I need to update if the columns exist in other tables. However the other table could just have a defualt value of 999, which mean any value. I will try and write the sudo code.

select row
from master table
where between dates
and cust is in ( custtable where cust matches or cust from custtable = 999)
and branch is in ( branchtable where branch matches or branch from branchtable = 999)
and product is in ( producttable where product matches or product from producttable = 999)

only if the 3 equate to being found should I have the row.

Please let me know you you require any more information as it may not have made sense

Thanks in advance. Go easy on me sa I am just learning

UPDATE

So I Have the following

select @SI_Id = SIC_IdSupportedInitiative, @SI_SuppId = SIC_IdSupplier,
 @sd = SIC_StartDate, @ed = SIC_EndDate, @SI_Amt = SIC_ClaimAmount
from SIC_SupportedInitiative
where SIC_IdSupportedInitiative = 1

select sc.ID, sc.[Actual Posting Date],sc.[Customer Account], 
 sc.[Accounting Branch],sc.[Product Code],sc.[SalesQ uantity],sc.SIC_TotalClaimAmount
From SalesCurrent sc
where sc.[Actual Posting Date] between @sd and @ed
and exists 
( select 1 from SIC_CustomerDetails c
    where c.SIC_IdSupportedInitiative = @SI_id
      and c.SIC_ICC_Customer = sc.[Customer Account] or c.SIC_ICC_Customer = 999 )
and exists
( select 1 from SIC_BranchDetails b
    where b.SIC_IdSupportedInitiative = @SI_id
      and b.SIC_IMO_Branch = sc.[Accounting Branch] or b.SIC_IMO_Branch = 999 )
and exists
( select 1 from SIC_ProductDetails p
    where p.SIC_IdSupportedInitiative = @SI_id
      and p.SIC_SupplierProductCode = sc.[Product Code] or 
          p.SIC_SupplierProductCode IN (SELECT sp.SIC_SupplierProductCode 
                                          FROM SIC_SupplierProducts sp 
                                         WHERE sp.SIC_IdSupplier = @SI_SuppId 
                                           AND sp.SIC_SupplierProductCode = sc.[Product Code])
)

then I was going to loop round as I need to update the SIC_TotalClaimAmount 

to be SIC_TotalClaimAMount = SIC_TotalClaimAmount + ( sc.[Sales Quantity] * @SI_Amt )

Again if there is an easier way would love to hear.

Thanks

Upvotes: 0

Views: 123

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

I don't know what the rest of the query looks like, but you can do the logic you ware looking for using exists:

from mastertable mt
where between dates and
      exists (select 1 from custtable c where c.cust = mt.cust or c.cust = 999) and
      exists (select 1 from branchtable b where b.branch = mt.branch or b.branch = 999) and
      exists (select 1 from producttable p where p.product = mt.product or p.product = 999)

Upvotes: 3

Related Questions