A statistical report SQL problem

Today, my friend asked me a SQL question for a statistical report. I've been doing this for a long time. It was finally written out. Here's the record.

Problem:

ID--------------name--------------addtime
1 a 2014-01-01 02:30:21
1 b 2014-01-03 03:20:02
1 c 2014-01-03 06:10:30
1 D 2014-01-06 11:30:15
1 e 2014-01-06 11:10:18
1 F 2014-01-06 12:30:19
To reach the query result:
Addtime---------------Count
2014-01-01 on 1
2014-01-02 on 0
2014-01-03 on 2
2014-01-04 on 0
2014-01-05 on 0
2014-01-06 3 The main difficulty is: 2014.1.2 This day no data, how to sort by this date? The final idea is: a temporary table of dates, sorted by the temporal table of the date. I created the table according to my friend's needs: table Data: Code:

--Delete temporary tableDROP TABLE#tempDate--Create a temporary tableCREATE TABLE#tempDate (createtimeDATETIME)--Set start timeDECLARE @startTime DATETIMEDECLARE @endTime DATETIMESET @startTime='2015-3-10'SET @endTime='2015-4-28'--Generate Date Data while @startTime<@endTimeBEGIN    INSERT  into#tempDate (createtime)VALUES(CAST(DATEPART(YY,@startTime) as NVARCHAR( -))+'-'+    CAST(DATEPART(MM,@startTime) as NVARCHAR( -))+'-'+    CAST(DATEPART(DD,@startTime) as NVARCHAR( -))--Createtime-datetime              )    SET @startTime=DATEADD(DD,1,@startTime)    CONTINUEEND--Query Statistics ResultsSELECT#tempDate. Createtime,COUNT(B.createtime) Sumcount from#tempDate Left JOINtesttable1b on(CAST(DATEPART(yy, #tempDate. Createtime) as NVARCHAR( -))+'-'+    CAST(DATEPART(mm, #tempDate. Createtime) as NVARCHAR( -))+'-'+    CAST(DATEPART(DD, #tempDate. Createtime) as NVARCHAR( -)))=(CAST(DATEPART(Yy,b.createtime) as NVARCHAR( -))+'-'+    CAST(DATEPART(Mm,b.createtime) as NVARCHAR( -))+'-'+    CAST(DATEPART(Dd,b.createtime) as NVARCHAR( -)))

A statistical report SQL problem