Why is this SQL summing values that are way too great?

Question

I've got this SQL (adapted from here):

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;

...which (eventually, after much churning) gives me data like this:

The "Uchi" Unit, for example, shows "New" sales of over a billion dollars and "Existing" sales of 77 million. This is not really so, as this shows:

If I try to see exactly how much there is for each Subcategory, this way:

select monthlysales from ReportingMonthlySales RMS
left join CustomerCategoryLog CCL on RMS.Unit = CCL.Unit
where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'New'
--where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'Existing'
--where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'Assumed'
--where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'Organic'
--where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'Exploding'

...I end up with "0 items" regardless of which Subcategory value I query for. Even this WHERE clause:

where RMS.unit = 'Uchi' and Subcategory = 'New'

...turns up nothing.

Why is the sum so astronomical? How can I get it to calculate a more reasonable value (preferaby the right one), and not only extremes of either nothing or "everything"?

score 2 · Accepted Answer · answered Mar 27 '17 at 23:19

2

Your JOIN condition is matching multiple rows. That might be a property of the data, but your ON clause is problematic. You have:

on u.Unit = ccl.Unit and
   @paramdate >= ccl.begindate and
   @paramdate <= ccl.enddate OR ccl.enddate is null

You intend:

on u.Unit = ccl.Unit and
   @paramdate >= ccl.begindate and
   (@paramdate <= ccl.enddate OR ccl.enddate is null)

answered Mar 27 '17 at 23:19

Gordon Linoff

1,242,037
58
646
786

That helped; I still have some doubtlessly bogus values, though, such as over 2 billions in that one month for Applebees, etc. I'll czech it out more thoroughly tomorrow. – B. Clay Shannon-B. Crow Raven Mar 27 '17 at 23:27

Why is this SQL summing values that are way too great?

1 Answers1

Linked