I’m going to get the ten day sales report and i want to return 0 if there is not record for each day

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Sp_Rsv_DailyContractReport]
    @ContractDate datetime =null,  
    @RegisterUsersId varchar(32) = null

AS   

    Select    

    IsNull (SUM( c.Price) ,0) As 'TotalPrice', 
    IsNull(COUNT(c.[Id]) ,0) As 'ContractCount'     
    from Rsv_Contract as c

    where 
    c.ContractDate >= @ContractDate and  --'09/15/2019' ten day before till know
    c.RegisterUsersId = @RegisterUsersId and    
    c.contractstate in (1,2) and 
    c.ContractNumber!='0000000' and c.ContractNumber!='-1'

    Group by CAST(c.ContractDate AS DATE)   

This is my code that works fine but returns data only for the days the contract exists I want to get value for each day and return zero if it doesn’t any record for that day

Answer

At First Create a table which will hold all the 10 Days From your Contract Date,then Left join the result set of your Query with this Table as follows,

    Declare @start Date, @end Date
    Select @start=DATEADD(DAY,-10,@ContractDate), @end=@ContractDate

    ;With ContractDates( ContractDate ) as
    (
        Select @start as ContractDate
            union all
        Select Cast(DATEADD(DAY,1,ContractDate) as DATE)
            from ContractDates
            where ContractDate < @end
    )
    Select 
    ISNull(ct.TotalPrice,0) As 'TotalPrice' ,IsNull(ct.ContractCount,0) AS 'ContractCount' 
    from ContractDates cd 
    left join 
    (  
        Select    
        IsNull (SUM( c.Price) ,0) As 'TotalPrice', 
        IsNull(COUNT(c.[Id]) ,0) As 'ContractCount'    
        CAST(c.ContractDate AS DATE)    
        from Rsv_Contract as c
        where 
        c.ContractDate between DATEADD(DAY,-10,@ContractDate) and @ContractDate and
        c.RegisterUsersId = @RegisterUsersId and    
        c.Contractstate in (1,2) and 
        c.ContractNumber!='0000000' and c.ContractNumber!='-1'
        Group by CAST(c.ContractDate AS DATE) 
    )ct on cd.ContractDate=ct.ContractDate