declare @startdate datetime,@enddate datetime
set @startdate=dateadd(mm,datediff(mm,0,getdate()),0)
set @enddate=dateadd(ms,-2,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))

select convert(varchar(10),dateadd(day,number,@startdate),120) as col
from
    master..spt_values 
where 
    datediff(day,dateadd(day,number,@startdate), @enddate)>=0
    and number>=0 
    and type='p'

/*col
----------
2011-11-01
2011-11-02
2011-11-03
2011-11-04
2011-11-05
2011-11-06
2011-11-07
2011-11-08
2011-11-09
2011-11-10
2011-11-11
2011-11-12
2011-11-13
2011-11-14
2011-11-15
2011-11-16
2011-11-17
2011-11-18
2011-11-19
2011-11-20
2011-11-21
2011-11-22
2011-11-23
2011-11-24
2011-11-25
2011-11-26
2011-11-27
2011-11-28
2011-11-29
2011-11-30

(30 行受影响)



*/

------解决方案--------------------

declare @startdate datetime,@enddate datetime
set @startdate=dateadd(mm,datediff(mm,0,getdate()),0)
set @enddate=dateadd(ms,-2,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))

select convert(varchar(10),dateadd(day,number,@startdate),120) as col
from
   master..spt_values  
where  
   datediff(day,dateadd(day,number,@startdate), @enddate)>=0
   and number>=0  
   and type='p'


------解决方案--------------------

SQL code
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#salesOrder') is null
    drop table #salesOrder
Go
Create table #salesOrder([id] int,[name] nvarchar(1),[createDate] Datetime,[sale] int)
Insert #salesOrder
select 1,N'A','2011-11-11 12:10:10',2 union all
select 2,N'A','2011-11-12 13:10:10',4 union all
select 3,N'A','2011-11-14 13:10:10',4 union all
select 4,N'B','2011-11-14 13:10:10',4 union all
select 5,N'B','2011-11-18 13:10:10',4
Go
if not object_id(N'Tempdb..#Staff') is null
    drop table #Staff
Go
Create table #Staff([id] int,[name] nvarchar(1))
Insert #Staff
select 1,N'A' union all
select 2,N'B' union all
select 3,N'C'
Go
DECLARE @dt1 DATETIME,@dt2 DATETIME
SET @dt1='2011-11-01'
SET @dt2='2011-11-30'

;WITH dt
AS
(
SELECT @dt1 AS dt
UNION ALL
SELECT dt+1 FROM dt WHERE dt<@dt2
)
Select 
a.[name],CONVERT(VARCHAR(10),b.dt,120) AS dt,ISNULL(SUM(c.sale),0) AS sale
from #Staff AS a
CROSS JOIN dt AS b 
LEFT JOIN #salesOrder AS c ON a.[name]=c.[name] AND DATEDIFF(d,b.dt,c.[createDate])=0
GROUP BY a.[name],CONVERT(VARCHAR(10),b.dt,120)
ORDER BY 1,2

------解决方案--------------------
SQL code
declare @startdate datetime,@enddate datetime
set @startdate=dateadd(mm,datediff(mm,0,getdate()),0)
set @enddate=dateadd(ms,-2,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))
;with f as
(
select
  isnull(a.name,b.name) as name,CONVERT(varchar(10),[createDate],120) as createDate,