I believe I have a rather unique issue with a query that I am trying to write that involves 4 tables. Specifically I am trying to add a CTE that determines the balance of an account by subtracting the sum total of transactions within a date range.

There is a table with experts that is linked to a contract table that contains the value of the contract. There is a case details table linked to the experts table and a case activities table linked to the case details table.

Experts=>Case_Details=>Case_activities
Experts=>Contract

The case_activities table contains the line item values while the contract table contains the contract value and start and end date of said contract.

I want to sum all activities within the date range of the valid contract and subtract that sum from the contract value to give the current balance available.

This is what I have written so far and the value returned is NULL.

ALTER PROCEDURE [dbo].[spSrchByLastName_CTE]
@lname VarChar(50),
@sortby Varchar(1)

AS
WITH
cteSelectExpert_UIDbyLastName(cteExpertID)
AS
(
SELECT Expert_UID
FROM Experts
WHERE LName like @lname + '%'
),
cteSelectContractInfo (
cteExpertID
,cteContractStatus
,cteContractBeginDate
,cteContractEndDate
,cteContractAmount
)
AS
(
SELECT ep.cteExpertID
,CASE ec.ContractStatus
WHEN 0 THEN 'INACTIVE'
ELSE 'ACTIVE'
END as ecSTATUS
,ec.ContractBegin
,ec.ContractEnd
,ec.ContractAmount
FROM cteSelectExpert_UIDbyLastName AS ep INNER JOIN
dbo.Expert_Contract AS ec on ep.cteExpertID = ec.Expert_UID
),
cteGetActivitySumTotal (
cteExpertID
,cteActivityDate
,cteTotalActivityCost
)
AS
(
SELECT ep.cteExpertID
,eca.Activity_Date
,SUM(eca.Total) AS TotalActivityCost
FROM cteSelectExpert_UIDbyLastName AS ep INNER JOIN
dbo.Case_Details ON ep.cteExpertID = dbo.Case_Details.Expert_UID INNER JOIN
dbo.Case_ActivityHours AS eca ON dbo.Case_Details.CaseDetail_UID = eca.CaseDetail_UID
GROUP BY ep.cteExpertID
,eca.Activity_Date
),
cteGetContractBalance (
cteExpertID
,cteContractBalance
)
AS
(
SELECT eci.cteExpertID
,eci.cteContractAmount - east.cteTotalActivityCost as ContractBalance
FROM cteSelectContractInfo AS eci INNER JOIN
cteGetActivitySumTotal AS east ON east.cteExpertID = eci.cteExpertID
WHERE east.cteActivityDate >= eci.cteContractBeginDate AND east.cteActivityDate <= eci.cteContractEndDate
),
cteSelectExpertsData (
cteExpertID
,cteLastName
,cteFirstName
,cteLicense
,cteAvailable
,cteDO_NOT_USE
,cteTitle
,cteCountyName
,cteZip
,cteStatus
,cteCase_Count
,cteRating
)
AS
(
SELECT cteExpertID
,ep.License
,ep.FName
,ep.LName
,ep.Available
,ep.DO_NOT_USE
,rt.Title
,rz.CountyName
,rz.Zip
,et.StatusType_UID
,dbo.vExpertCases_Count.Cases_Count
,dbo.vExpertRatingsAverage.Rating
FROM cteSelectExpert_UIDbyLastName INNER JOIN
dbo.Experts AS ep ON cteExpertID = ep.Expert_UID INNER JOIN
dbo.Expert_Specialties AS es ON ep.Expert_UID = es.Expert_UID INNER JOIN
dbo.Expert_Status AS et ON ep.Expert_UID = et.Expert_UID LEFT OUTER JOIN
dbo.RefZipCodes AS rz ON ep.Zip = rz.Zip LEFT OUTER JOIN
dbo.RefTitle AS rt ON ep.Title_UID = rt.Title_UID LEFT OUTER JOIN
dbo.vExpertCases_Count ON ep.Expert_UID = vExpertCases_Count.Expert_UID LEFT OUTER JOIN
dbo.vExpertRatingsAverage ON ep.Expert_UID = vExpertRatingsAverage.Expert_UID
GROUP BY cteExpertID
,ep.License
,ep.FName
,ep.LName
,ep.Available
,ep.DO_NOT_USE
,rt.Title
,rz.CountyName
,rz.Zip
,et.StatusType_UID
,dbo.vExpertCases_Count.Cases_Count
,dbo.vExpertRatingsAverage.Rating
HAVING (et.StatusType_UID = 1) AND (ep.LName like @lname + '%')

)

/* SET NOCOUNT ON */
SELECT sed.cteExpertID
,sed.cteLastName
,sed.cteFirstName
,sed.cteLicense
,sed.cteAvailable
,sed.cteDO_NOT_USE
,sed.cteTitle
,sed.cteCountyName
,sed.cteZip
,sed.cteStatus
,sed.cteCase_Count
,sed.cteRating
,gast.cteTotalActivityCost
,sci.cteContractStatus
,sci.cteContractAmount
,gcb.cteContractBalance
FROM cteSelectExpertsData AS sed INNER JOIN
cteSelectContractInfo as sci ON sci.cteExpertID = sed.cteExpertID LEFT OUTER JOIN
cteGetContractBalance as gcb ON gcb.cteExpertID = sed.cteExpertID LEFT OUTER JOIN
cteGetActivitySumTotal as gast on gast.cteExpertID = sed.cteExpertID
ORDER BY
case @sortby when '1' then cteLicense End,
case @sortby when '2' then cteLastName End,
case @sortby when '3' then cteRating End,
case @sortby when '4' then cteCountyName End,
case @sortby when '5' then cteZip End
;
RETURN


Everything works except the results from cteGetContractBalance

Any suggestions? I do have a nagging feeling that my expectations for SQL may be outside its scope

Thanks in advance