TSQL not pulling in complete data set

Question

I have a complicated stored procedure that worked great until the client wanted to change it.
I am not great with complicated TSQL so I have no idea what is wrong with my code.

Here is the situation. I have three temp tables, Cost, Adjustments, and Payments. In the end I merge all these tables together in a report table. The problem I am having is even if one or even two of these tables are null, as long as one table has data I need that data to show. I currently have it set up with full outer joins but I'm still not getting the full list, I'm missing probably....50 ish records that should be there.

Can anyone look at this code and tell me what the heck I'm doing wrong? I'm bringing all the data together on #ThisReportAll

UPDATE: So I removed the having clause to see what was going on, and the data for the overdue balance is returning null. So the math isn't...mathing correctly, any ideas?

CODE

 CREATE TABLE #BalanceAdjustmentsAll (CustomerId int, Amount decimal(20,2));
 CREATE TABLE #AnimalCostsAll (thisIndex int IDENTITY(1,1), AnimalTypeId int, Cost decimal(20,2));
 CREATE TABLE #TotalAnimalCostAll (thisIndex int IDENTITY(1,1), YearSetupId int, AnimalTypeId int, AnimalType varchar(max), OwnerId int, CustomerId int, AnimalCount int, TtlSpeciesCost decimal(20,2));
 CREATE TABLE #CustomerPaymentsAll (thisIndex int IDENTITY(1,1), CustomerID nvarchar(max), TtlPayments decimal(20,2));
 CREATE TABLE #CustomerInfoAll (thisIndex int IDENTITY(1,1), OwnerId int, CustomerId int, FName nvarchar(200), LName nvarchar(200),BName nvarchar(200));
 CREATE TABLE #ThisReportAll (thisIndex int IDENTITY(1,1), CustomerID nvarchar(max), Year char(4), OverdueBalance decimal(20,2), YearSetupId int);

 INSERT INTO #BalanceAdjustmentsAll (CustomerId, Amount)
 SELECT CustomerId, SUM(Amount)
 FROM BalanceAdjustment
 WHERE YearSetupId = 3
 GROUP BY CustomerId;

 /* GET Costs per Animal for 'This' yearID */
 INSERT INTO #AnimalCostsAll (AnimalTypeId, Cost)
 SELECT AnimalTypeId, Cost
 FROM PerCapitaFee
 WHERE YearSetupId = 3;

 /* GET animal type totals for owner per year */
 INSERT INTO #TotalAnimalCostAll (yearSetupId,AnimalTypeId,AnimalType,OwnerId,CustomerId,AnimalCount,TtlSpeciesCost)
 SELECT YearSetup.YearSetupId,AnimalCount.AnimalTypeId,AnimalType.ShortDescription,Owner.OwnerId,Report.CustomerId,AnimalCount.Count,(ac.Cost * AnimalCount.Count)
 FROM AnimalCount
 INNER JOIN #AnimalCostsAll as ac
 ON ac.AnimalTypeId = AnimalCount.AnimalTypeId
 INNER JOIN AnimalType
 ON AnimalCount.AnimalTypeId=AnimalType.AnimalTypeId
 INNER JOIN AnimalLocation
 ON AnimalLocation.AnimalLocationid=AnimalCount.AnimalLocationId
 INNER JOIN Owner
 ON Owner.OwnerId=AnimalLocation.OwnerId
 AND Owner.OwnerType = 'P'
 INNER JOIN Report
 ON Report.ReportId=Owner.ReportId
 INNER JOIN YearSetup
 ON Report.YearSetupId=YearSetup.YearSetupId
 INNER JOIN County
 ON County.CountyId=AnimalLocation.CountyId
 WHERE YearSetup.YearSetupId = 3 AND Report.Completed IS NOT NULL AND Report.CustomerId IS NOT NULL

 /* Get The total payments a customer has made */
 INSERT INTO #CustomerPaymentsAll (CustomerID,TtlPayments)
 SELECT BPS.CustomerId,SUM(BPS.Amount)
 FROM BatchPaymentSplit BPS
 LEFT JOIN BatchPayment bp ON BPS.BatchPaymentId=bp.BatchPaymentId
 LEFT JOIN Batch b ON bp.BatchId=b.BatchId
 WHERE BPS.CustomerId IS NOT NULL
 AND
 (
 ((b.BatchTypeId = 'M' OR b.BatchTypeId = 'C' OR b.BatchTypeId = 'E') AND (b.BatchStatusId = 'S'))
 OR
 ((b.BatchTypeId = 'B' OR b.BatchTypeId = 'N' OR b.BatchTypeId = 'R' OR b.BatchTypeId = 'T') AND (b.BatchStatusId = 'S' OR b.BatchStatusId='C'))
 )
 AND
 BPS.YearSetupId = 3
 GROUP BY BPS.CustomerId;

 /* Deal with the name/id stuff */
 INSERT INTO #CustomerInfoAll(FName, LName, BName, OwnerId, CustomerId)
 SELECT
 o.FirstName AS FName,
 o.LastName AS LName,
 o.BusinessName AS BName,
 o.OwnerId AS OwnerId,
 r.CustomerId AS CustomerId
 FROM Owner o
 INNER JOIN Report r
 ON o.ReportId = r.ReportId
 AND o.OwnerType = 'P'
 WHERE r.CustomerId IN (SELECT CustomerId FROM #TotalAnimalCostAll)
 AND r.Completed IS NOT NULL
 AND r.YearSetupId = 3
 AND NOT EXISTS(
 SELECT 1 FROM Report
 WHERE r.CustomerId = Report.CustomerId
 AND Report.Completed IS NOT NULL
 AND r.ReportId != Report.ReportId
 AND r.YearSetupId = Report.YearSetupId
 AND (
 r.Completed < Report.Completed
 OR (
 r.Completed = Report.Completed
 AND r.ReportId < Report.ReportId
 )
 )
 )
 ORDER BY CustomerId;

 /**  MAKE IT SO #1  **************************************************/
 /* Simply Joining The Customer Info to the calculated totals to avoid any aggregation shenanigans... */
 INSERT INTO #ThisReportAll (CustomerID,Year,OverdueBalance,YearSetupId)
 SELECT COALESCE(t.CustomerId,cp.CustomerId,ba.CustomerID), ys.Name AS Year, 
 CASE 
    WHEN (SUM(t.TtlSpeciesCost) < 5 AND SUM(t.TtlSpeciesCost) > 0) AND (ys.Name='2015' OR ys.Name='2016')
        THEN (5) - Isnull(cp.TtlPayments,0) + Isnull(ba.Amount,0) 
         ELSE SUM(t.TtlSpeciesCost) - Isnull(cp.TtlPayments,0) + Isnull(ba.Amount,0) 
 END
 AS TtlOwnerCost, t.YearSetupId AS YearSetupId
 FROM #TotalAnimalCostAll t
 FULL OUTER JOIN #CustomerPaymentsAll cp ON t.CustomerId=cp.CustomerID
 FULL OUTER JOIN #BalanceAdjustmentsAll ba ON COALESCE(t.CustomerId,cp.CustomerId)=ba.CustomerID
LEFT JOIN YearSetup ys ON COALESCE(t.CustomerId,cp.CustomerId,ba.CustomerID) = ys.YearSetupId     

GROUP BY COALESCE(t.CustomerId,cp.CustomerId,ba.CustomerID),ys.Name,cp.TtlPayments, ba.Amount, t.YearSetupId
 HAVING 
    CASE WHEN (SUM(t.TtlSpeciesCost) < 5 AND SUM(t.TtlSpeciesCost) > 0) AND (ys.Name='2015' OR ys.Name='2016')
        THEN SUM(5) - Isnull(cp.TtlPayments,0) + Isnull(ba.Amount,0)
         ELSE SUM(t.TtlSpeciesCost) - Isnull(cp.TtlPayments,0) + Isnull(ba.Amount,0)
     END < 0;

 /*  Return some meaningful report data */
 SELECT r.Year AS [YearName],r.CustomerID,left(ci.FName,20) AS [FirstName], left(ci.LName,40) AS [LastName], left(ci.BName,40) AS [BusinessName],r.OverdueBalance AS [Balance],r.YearSetupId
 FROM #ThisReportAll r
 LEFT JOIN #CustomerInfoAll ci ON r.CustomerID = ci.CustomerId
 ORDER BY CAST(r.CustomerID as int) ASC;

 DROP TABLE #BalanceAdjustmentsAll;
 DROP TABLE #AnimalCostsAll;
 DROP TABLE #TotalAnimalCostAll;
 DROP TABLE #CustomerPaymentsAll;
 DROP TABLE #CustomerInfoAll;
 DROP TABLE #ThisReportAll;

Answer 1

Found it. I didn't have a default value for t.TtlSpeciesCost if it was null

SUM(t.TtlSpeciesCost) - Isnull(cp.TtlPayments,0) + Isnull(ba.Amount,0)

to

SUM(ISNULL(t.TtlSpeciesCost,0)) - Isnull(cp.TtlPayments,0) + Isnull(ba.Amount,0)

Answer 2

Some missing records may be found here: by adjusting /* Get The total payments a customer has made */

INSERT INTO #CustomerPaymentsAll (CustomerID,TtlPayments)
 SELECT BPS.CustomerId,SUM(BPS.Amount)
 FROM BatchPaymentSplit BPS
 LEFT JOIN BatchPayment bp 
        ON BPS.BatchPaymentId=bp.BatchPaymentId
 LEFT JOIN Batch b 
        ON bp.BatchId=b.BatchId
       AND ((b.BatchTypeId IN ('M', 'C', 'E')   AND b.BatchStatusId = 'S')
        OR  (b.BatchTypeId IN ('B','N','R','T') AND (b.BatchStatusId IN ('S','C')))
 WHERE BPS.CustomerId IS NOT NULL
   AND BPS.YearSetupId = 3
 GROUP BY BPS.CustomerId;

The WHERE on B would have negated the left join causing null records to be omitted. or made the left join to behave like an inner join.

To know for certain we need sample data from your tables showing which records are being omitted that you need to retain.

I also refactored the OR's and made them "IN"s to improve readability.