you problem is that you are using the "CustomerOrder_DeliveryDate" which will have missing date values. what you need is a sequential table of dates, which is called a calendar table.
Something like this:
Declare @CalendarTable table
(
[DateKey] [int] NOT NULL,
[FullDateAlternateKey] [date] NOT NULL,
[DayNumberOfWeek] [tinyint] NOT NULL,
[EnglishDayNameOfWeek] [nvarchar](10) NULL,
[DayNumberOfMonth] [tinyint] NOT NULL,
[DayNumberOfYear] [smallint] NOT NULL,
[WeekNumberOfYear] [tinyint] NULL,
[EnglishMonthName] [nvarchar](10) NULL,
[MonthNumberOfYear] [tinyint] NOT NULL,
[CalendarQuarter] [tinyint] NOT NULL,
[CalendarYear] [smallint] NOT NULL,
[CalendarSemester] [tinyint] NULL
)
Declare @StartDate datetime
Declare @EndDate datetime
Select @StartDate = '20150101',
@EndDate = '20151231'
;WITH cTally
AS
(
select top 1100 ROW_NUMBER() Over (order by (select null)) - 1 as num
from syscolumns c
cross join syscolumns cc
)
INSERT INTO @CalendarTable(DateKey, FullDateAlternateKey, DayNumberOfWeek,
EnglishDayNameOfWeek, DayNumberOfMonth, DayNumberOfYear,
WeekNumberOfYear, EnglishMonthName, MonthNumberOfYear,
CalendarQuarter, CalendarYear)
SELECT
CONVERT(INT, CONVERT(nvarchar(12), DATEADD(dd,num,@StartDate), 112)),
DATEADD(dd,num,@StartDate),
DATEPART(dw, DATEADD(dd,num,@StartDate)),
DATENAME(dw, DATEADD(dd,num,@StartDate)),
DATEPART(dd, DATEADD(dd,num,@StartDate)),
DATEPART(dayofyear, DATEADD(dd,num,@StartDate)),
DATEPART(week, DATEADD(dd,num,@StartDate)),
DATENAME(MONTH, DATEADD(dd,num,@StartDate)),
DATEPART(mm, DATEADD(dd,num,@StartDate)),
DATEPART(qq, DATEADD(dd,num,@StartDate)),
DATEPART(yyyy, DATEADD(dd,num,@StartDate))
from cTally
Where DATEADD(dd,num,@StartDate) <= @EndDate
now just simply LEFT JOIN this table with you runtime created table and you will get the proper result.
something like this.
;with OrderDetail
AS
(
select '20151005' AS orderdate, 1 as orderqty union all
select '20150105' AS orderdate, 2 as orderqty union all
select '20150106' AS orderdate, 3 as orderqty union all
select '20150107' AS orderdate, 4 as orderqty union all
select '20150109' AS orderdate, 5 as orderqty union all
select '20150110' AS orderdate, 6 as orderqty union all
select '20150111' AS orderdate, 7 as orderqty union all
select '20150112' AS orderdate, 8 as orderqty union all
select '20150114' AS orderdate, 9 as orderqty union all
select '20150116' AS orderdate, 10 as orderqty union all
select '20150118' AS orderdate, 11 as orderqty
)
select c.FullDateAlternateKey, sum(orderqty) as orderqty
from @CalendarTable c
left join OrderDetail o on c.FullDateAlternateKey = o.orderdate
group by c.FullDateAlternateKey
order by c.FullDateAlternateKey
hope it helps