Why do these sum operations not "add up" as they should?

Question

Note: This is a follow[on,up] to this question and this question and this question.

The responses all moved me forward, but even that last one, although it improved matters, did not completely solve this issue.

This query:

select monthlysales, MemberNo from ReportingMonthlySales
where unit = 'Abuelos' and CYear = 2017 and cmonth = 3

...returns a bunch of rows, where the Monthly Sales total is $364,121.69

However, when I try to compartmentalize these monthly sales into four categories, although the sum of them all should be the same, the sum is actually astronomically "huger" - the following code returns the vastly inflated value of 23,924,211.30

24 millions of dollars is almost 70 times as much as the 364 thousand that is the actual sum total. Why is this "crazy" inflation occurring?

Here is the fancy pants but apparently bamboozled code:

declare @Unit varchar(30);
declare @Year int = 2017;
declare @Month int = 3;
declare @paramdate datetime;
set @paramdate = convert(datetime,convert(char(4),@Year)
                +right('0'+convert(varchar(2),@month),2)
                +'01') 

IF OBJECT_ID('tempdb.dbo.#Units', 'U') IS NOT NULL
DROP TABLE #Units

select distinct unit
into #Units
from ReportingMonthlySales;

select 
    u.Unit
  , New      = sum(case when ccl.Subcategory = 'New'      then rms.MonthlySales else 0 end)
  , Assumed  = sum(case when ccl.Subcategory = 'Assumed'  then rms.MonthlySales else 0 end)
  , Existing = sum(case when ccl.Subcategory = 'Existing' then rms.MonthlySales else 0 end)
  , Organic  = sum(case when ccl.Subcategory = 'Organic'  then rms.MonthlySales else 0 end)
from #Units u
  left join CustomerCategoryLog ccl 
    on u.Unit = ccl.Unit
   and @paramdate >= ccl.begindate and
   (@paramdate <= ccl.enddate OR ccl.enddate is null)
  left join ReportingMonthlySales rms
    on u.Unit = rms.Unit
   and rms.cyear  = @year
   and rms.cmonth = @month
group by u.unit;

The two tables queried are:

CustomerCategoryLog
-------------------
MemberNo (VarChar)
Unit (VarChar)
Custno (VarChar)
Category (VarChar)
Subcategory (VarChar)
BeginDate (DateTime)
EndDate (DateTime)
ChangedBy (VarChar)
ChangedOn (DateTime)

ReportingMonthlySales
---------------------
AutoID (Int)
Unit (VarChar)
MemberNo (VarChar)
NumUnits (Int)
MonthlySales (Money)
CYear (Int)
Cmonth (Int)
CreateDate (DateTime)

Is there something faulty in the fancy-pants sql, or ... ?!?

Answer 1

I suspect the left joins and Or ccl.enddate is null generated a 1 to many

Perhaps a straight inner join will correct the inflation

select 
    u.Unit
  , New      = sum(case when ccl.Subcategory = 'New'      then rms.MonthlySales else 0 end)
  , Assumed  = sum(case when ccl.Subcategory = 'Assumed'  then rms.MonthlySales else 0 end)
  , Existing = sum(case when ccl.Subcategory = 'Existing' then rms.MonthlySales else 0 end)
  , Organic  = sum(case when ccl.Subcategory = 'Organic'  then rms.MonthlySales else 0 end)
from #Units u
join CustomerCategoryLog   ccl on u.Unit = ccl.Unit and @paramdate >= ccl.begindate and @paramdate <= ccl.enddate
join ReportingMonthlySales rms on u.Unit = rms.Unit and rms.cyear  = @year and rms.cmonth = @month
group by u.unit;

Answer 2

You are doing a join between the RMS table and these other tables. Your code assumes it will find one record for each record in the RMS table. This is not true. This is the cause of getting multiple rows reported and summed. I would think it is most likely that for a particular unit and @paramdate, there is multiple CCL entries.

Answer 3

i think this is causing the duplication

select distinct unit
into #Units
from ReportingMonthlySales;

you basically inserted the same data from the table and used at the left join..

try this updated script

  SELECT
  rms.Unit,
  New = SUM(CASE
    WHEN ccl.Subcategory = 'New' THEN rms.MonthlySales
    ELSE 0
  END),
  Assumed = SUM(CASE
    WHEN ccl.Subcategory = 'Assumed' THEN rms.MonthlySales
    ELSE 0
  END),
  Existing = SUM(CASE
    WHEN ccl.Subcategory = 'Existing' THEN rms.MonthlySales
    ELSE 0
  END),
  Organic = SUM(CASE
    WHEN ccl.Subcategory = 'Organic' THEN rms.MonthlySales
    ELSE 0
  END)
FROM ReportingMonthlySales rms
inner JOIN CustomerCategoryLog ccl
  ON rms.Unit = ccl.Unit
where
  AND @paramdate >= ccl.begindate
  AND (@paramdate <= isnull(ccl.enddate,getdate())
  )
 AND
   rms.cyear = @year
  AND rms.cmonth = @month
GROUP BY rms.unit;

Answer 4

Your CustomerCategoryTable as what its name implies could have multiple records for different CustNo for the same UnitNo and that makes your first left join returns duplicate rows which will multiplies the monthly sales, and what I think is that there is no solution for your query except if you try to fix the schema itself for one simple information is that you are trying to find the monthly sales for each unit for it's different subcategorues and your ReportingMonthlySales table doesn't have any information regarding the subcategories.