# Miscellaneous > Structured Query Language (SQL) >  SQL Query involving date range

## Dagdason

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

----------

