updating a sum of one table based on input from another table

Question

Hello,

We've the following SQL that we use to calculate total costs.

 SELECT 
    DailyProduction.CustomerId as CustomerId, 
    SUM(Resource.CostPerUnit * DailyProduction.UnitsToStorage + Resource.CostPerUnit * DailyProduction.UnitsToMarket) AS TotalCost
 FROM 
    dbo.hgm_ResourceTypes Resource
 JOIN
    dbo.hgm_ResourceDailyProduction DailyProduction
 ON
    Resource.ResourceId = DailyProduction.ResourceId
 GROUP BY
    DailyProduction.CustomerId

As is evident, we gather data from two different tables to calculate the total cost of production for each customer.

We now have a requirement to add another table to this mix. This new table identifies the discount that applies to each resource per customer. Furthermore, multiple rows may refer to the same customer and the same resource. In such a case, all discounts must be summed together to identify the total discount.

Eg:

CUSTOMER: 1  RESOURCE: 1   DISCOUNT: 1
CUSTOMER: 1  RESOURCE: 1   DISCOUNT: 3
CUSTOMER: 1  RESOURCE: 2   DISCOUNT: 5

So, we have to identify the total discount per customer for each resource (that's fairly easy to do for me). And then use that discount in the SQL above and deduct it from the CostPerUnit for that particular resource when calculating the TotalCost column (hopefully that makes sense). I've been trying all sorts of joins and I'm hoping someone here can help me with this. Any help is much appreciated.

Thanks!

Answer 1

In this solution, I'm using a new feature introduced in SQL Server 2005 which is the OUTER APPLY operator. This allows you to reference columns in the outer tables. I'm using OUTER APPLY instead of CROSS APPLY to account for the possibility that there are no discount rows.

Select  DailyProduction.CustomerId as CustomerId
    ,  SUM(Resource.CostPerUnit * DailyProduction.UnitsToStorage 
        + Resource.CostPerUnit * DailyProduction.UnitsToMarket) AS TotalCost
    , Coalesce(Discount.Total, 0) As TotalDiscount
 From  dbo.hgm_ResourceTypes As Resource
    Join dbo.hgm_ResourceDailyProduction As DailyProduction
        On Resource.ResourceId = DailyProduction.ResourceId
    Outer Apply (
                Select Sum(D1.Discount) As Total
                From dbo.hgm_Discounts As D1
                Where D1.CustomerId = DailyProduction.CustomerId
                    And D1.ResourceId = Resource.ResourceId
                ) As Discount
 Group By DailyProduction.CustomerId

An alternate approach would be to use a derived table:

 Select  DailyProduction.CustomerId as CustomerId
    ,  SUM(Resource.CostPerUnit * DailyProduction.UnitsToStorage 
        + Resource.CostPerUnit * DailyProduction.UnitsToMarket) AS TotalCost
    , Coalesce(Discount.Total, 0) As TotalDiscount
 From  dbo.hgm_ResourceTypes As Resource
    Join dbo.hgm_ResourceDailyProduction As DailyProduction
        On Resource.ResourceId = DailyProduction.ResourceId
    Left Join   (
                Select D1.CustomerId, D1.ResourceId, Sum(D1.Discount) As Total
                From dbo.hgm_Discounts As D1
                Group By D1.CustomerId, D1.ResourceId
                ) As Discount
        On Discount.CustomerId = DailyProduction.CustomerId
            And Discount.ResourceId = Resource.ResourceId
 Group By DailyProduction.CustomerId

Answer 2

Another approach

;WITH Discounts AS
(
  SELECT ResourceID, CustomerID, SUM(Discounts) AS TotalDiscount
  FROM dbo.hgm_Discounts
  GROUP BY ResourceID, CustomerID
)
SELECT DailyProduction.CustomerId as CustomerId,
  SUM((Resource.CostPerUnit - COALESCE(Discounts.TotalDiscount ,0)) * DailyProduction.UnitsToStorage
     + (Resource.CostPerUnit - COALESCE(Discounts.TotalDiscount ,0)) * DailyProduction.UnitsToMarket) AS TotalCost
FROM dbo.hgm_ResourceTypes Resource 
INNER JOIN dbo.hgm_ResourceDailyProduction DailyProduction 
    ON Resource.ResourceId = DailyProduction.ResourceId 
LEFT OUTER JOIN Discounts
    ON Resource.ResourceId = Discounts.ResourceId
   AND DailyProduction.CustomerId = Discounts.CustomerId
GROUP BY
    DailyProduction.CustomerId