生成指定日期段的日期列表,月份列表

if exists(select * from sys.sysobjects where id = OBJECT_ID(N'[dbo].[f_getdate]') and xtype in (N'FN',N'IF',N'TF'))
drop function [dbo].[f_getdate]
go
/*--生成列表
生成指定日期段的日期列表


--查询工作日
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',0)

--查询休息日
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',1)

--查询全部日期
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',NULL)
--*/


create function dbo.f_getdate(
@begin_date datetime,
@end_date datetime,
@bz bit
)returns @re table(id int identity(1,1),Date datetime,Weekday nvarchar(13))
as 
begin
declare @tb table(id int identity(0,1), a bit)
insert into @tb select top 366 0 from sysobjects a,sysobjects b


if @bz = 0
while @begin_date <= @end_date 
begin
insert into @re(Date,Weekday)
select Date,DATENAME(Weekday,Date)
from (
select Date = DATEADD(day,id,@begin_date)
 from @tb
) a where Date <= @end_date and 
(DATEPART(Weekday,Date) + @@DATEFIRST - 1) % 7 between 1 and 5
set @begin_date = DATEADD(day,366,@begin_date)
end
else if @bz = 1
while @begin_date <=@end_date 
begin
insert into @re(Date,Weekday)
select Date,DATENAME(Weekday,Date) from
(
select Date = DATEADD(day,id,@begin_date)
from @tb
) a where Date <= @end_date 
and (DATEPART(Weekday,Date) + @@DATEFIRST - 1) % 7 in (0,6)
set @begin_date = DATEADD(day,366,@begin_date)
end
else
while @begin_date < @end_date 
begin
insert into @re(Date,Weekday)
select Date,DATENAME(Weekday,Date) from
(
select Date = DATEadd(day,id,@begin_date)
from @tb
) a where Date <= @end_date 
set @begin_date = DATEADD(day,366,@begin_date)
end
return
end
go

SELECT * FROM dbo.f_getdate('2012-12-31','2013-12-31',0)

=========================

use sqltest
go
declare @t Table(id int,date datetime,[money] decimal(10,2))
insert into @t select 1,'2005-1-1',10
union all select 2,'2005-2-21',20
union all select 3,'2005-3-1',30
union all select 2,'2005-3-1',40
union all select 3,'2005-5-1',50
union all select 2,'2005-11-21',60
union all select 3,'2005-11-11',70


select a.[MONTH],[money] = ISNULL(b.[money],0)
from (
select [MONTH] = 1 union all
select [MONTH] = 2 union all
select [MONTH] = 3 union all
select [MONTH] = 4 union all
select [MONTH] = 5 union all
select [MONTH] = 6 union all
select [MONTH] = 7 union all
select [MONTH] = 8 union all
select [MONTH] = 9 union all
select [MONTH] = 10 union all
select [MONTH] = 11 union all
select [MONTH] = 12
)a
left join (
select [month]= MONTH(Date),[money]= SUM([money])
from @t group by MONTH(Date) 
) b on a.[month] = b.[month]