CODEWITHSUNDEEP
sqlsql-serverstored-procedurescase
Liam neesan
Liam neesan

Reputation: 2551

How to use CASE in my Scenario in SQL Server?

I have to calculate total quantity with certain cases.

#customerSales table:

ItemLookupCode  TotalQuantity  
-----------------------------
111A000         20           
111B001         320          
111D000         5            
111D001         5            
111D002         1            
111D003         1

#temp_ItemRelations table

ID  ItemLookupCode  ChildItemLookupCode1   Child1Piece    ChildItemLookupCode2  Child2Piece   ChildItemLookupCode    Child3Piece
---------------------------------------------------------------------------------------------------------------
352 111A000         111A001                5                       111A002         3                   NULL             NULL    
365 111B000         111B001                4                       NULL          NULL                  NULL             NULL    
377 111C000         111C001                10                      111C002         5                  111C003            1  
412 111D000         111D001                12                      111D002         8                  111D003            4      
601 111E000         111E001                16                      111E002         6                  111E003            1

You can see 111D000 of ItemLookupCode in #temp_ItemRelations, all children are sold including parent.

So output should be like below

 ItemLookupCode     TotalQuantity    //parent    + child1 + child2 + child3
 111D000                77           //  5       + (5*12) + (1*8)  + (1*4)
 111A000                20           //  20      + no child
 111B000               1280          // noparent + (4*320)+ nochild2 & 3

I tried the following query. But I don't how to sum those values. I know the following query is wrong. But I tried.

SELECT
    ir.ItemLookupCode,
    (CASE 
        WHEN cs.ItemLookupCode = ir.ItemLookupCode 
           THEN cs.TotalQuantity
        WHEN cs.itemlookupcode = ir1.childitemlookupcode1 
           THEN (cs.TotalQuantity * ir.Child1Piece)
        WHEN cs.itemlookupcode = ir2.childitemlookupcode2 
           THEN (cs.TotalQuantity * ir.Child2Piece)
        WHEN cs.itemlookupcode = ir3.childitemlookupcode 
           THEN (cs.TotalQuantity * ir.Child3Piece)
        ELSE 0 
     END) AS TotalQuantity
FROM
    #temp_ItemRelations ir
LEFT JOIN
    #customerSales cs ON cs.itemlookupcode = ir.itemlookupcode 
LEFT JOIN
    #temp_ItemRelations ir1 ON cs.itemlookupcode = ir1.childitemlookupcode1
LEFT JOIN
    #temp_ItemRelations ir2 ON cs.itemlookupcode = ir2.childitemlookupcode2
LEFT JOIN
    #temp_ItemRelations ir3 ON cs.itemlookupcode = ir3.childitemlookupcode

Upvotes: 1

Views: 58

Answers (2)

Luuk
Luuk

Reputation: 14929

When you do

CASE WHEN condition1 THEN expression1 WHEN condition2 THEN expression2 ...

This will mean that when condition1 is TRUE, the result will be expression1. There will only be a check for condition2 when condition1 is FALSE.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You have a bad data structure. Your item relations should have one row per child relationship, not multiple columns with numbers to distinguish them.

I wouldn't use case for this calculation at all, just coalesce(). More importantly, you seem to have the joins to the wrong tables:

select ir.ItemLookupCode,
       (coalesce(cs.TotalQuantity, 0) +
        coalesce(cs1.TotalQuantity * ir.Child1Piece, 0) +
        coalesce(cs2.TotalQuantity * ir.Child2Piece, 0) +
        coalesce(cs3.TotalQuantity * ir.Child3Piece, 0)
       ) as TotalQuantity
from #temp_ItemRelations ir left join
      #customerSales cs
      on cs.itemlookupcode = ir.itemlookupcode left join
      #customerSales cs1
      on cs1.itemlookupcode = ir.childitemlookupcode1 left join
      #customerSales cs2
      on cs2.itemlookupcode = ir.childitemlookupcode2 left join
      #customerSales cs3
      on cs3.itemlookupcode = ir.childitemlookupcode3;

Upvotes: 4

Related Questions