查寻日期问题
小弟请教一个查寻的日期问题，问题如下：
以下是十一月的销售报告，我如何可以得到左面是具体日期(例如: 11/1/2006)，右边是具体销售额的查寻结果呢？请注意下面的数据中１２日没有销售数据，如何能在左侧显示１１月的所有日期，而且将１２日的数据显示为０，而不是NULL.
谢谢！
1 $1,722.97
2 $2,161.02
3 $2,090.73
4 $2,213.71
6 $1,759.49
7 $1,713.14
8 $1,701.17
9 $2,291.87
10 $2,040.68
11 $2,123.06
13 $1,864.47
14 $1,750.59
15 $1,948.97
16 $2,148.17
17 $2,026.26
18 $2,032.09
20 $1,897.51
21 $1,771.48
22 $1,896.66
23 $2,024.81
24 $1,882.29
25 $1,976.44
27 $1,724.65
28 $1,697.80
29 $1,768.01
30 $2,210.79
[解决办法]
--准备工作，建一个函数
/*--生成列表
生成指定日期段的日期列表
--邹建 2005.03(引用请保留此信息)--*/
/*--调用示例
--查询工作日
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 --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
)RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
AS
BEGIN
DECLARE @tb TABLE(ID int IDENTITY(0,1),a bit)
INSERT INTO @tb(a) 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
--创建测试环境
create table #t(id int,amount varchar(20))
--插入测试数据
insert #t(id,amount)
select '1 ', '$1,722.97 ' union all
select '2 ', '$2,161.02 ' union all
select '3 ', '$2,090.73 ' union all
select '4 ', '$2,213.71 ' union all
select '6 ', '$1,759.49 ' union all
select '7 ', '$1,713.14 ' union all
select '8 ', '$1,701.17 ' union all
select '9 ', '$2,291.87 ' union all
select '10 ', '$2,040.68 ' union all
select '11 ', '$2,123.06 ' union all
select '13 ', '$1,864.47 ' union all
select '14 ', '$1,750.59 ' union all
select '15 ', '$1,948.97 ' union all
select '16 ', '$2,148.17 ' union all
select '17 ', '$2,026.26 ' union all
select '18 ', '$2,032.09 ' union all
select '20 ', '$1,897.51 ' union all
select '21 ', '$1,771.48 ' union all
select '22 ', '$1,896.66 ' union all
select '23 ', '$2,024.81 ' union all
select '24 ', '$1,882.29 ' union all
select '25 ', '$1,976.44 ' union all
select '27 ', '$1,724.65 ' union all
select '28 ', '$1,697.80 ' union all
select '29 ', '$1,768.01 ' union all
select '30 ', '$2,210.79 '
--求解过程
select _d.date,_t.amount
from f_getdate( '20061101 ', '20061130 ',null) _d
left join #t _t on day(_d.date) = _t.id
--删除测试环境
drop table #t
/*--测试结果
date amount
------------------------------------------------------ --------------------
2006-11-01 00:00:00.000 $1,722.97
2006-11-02 00:00:00.000 $2,161.02
2006-11-03 00:00:00.000 $2,090.73
2006-11-04 00:00:00.000 $2,213.71
2006-11-05 00:00:00.000 NULL
2006-11-06 00:00:00.000 $1,759.49
2006-11-07 00:00:00.000 $1,713.14
2006-11-08 00:00:00.000 $1,701.17
2006-11-09 00:00:00.000 $2,291.87
2006-11-10 00:00:00.000 $2,040.68
2006-11-11 00:00:00.000 $2,123.06
2006-11-12 00:00:00.000 NULL
2006-11-13 00:00:00.000 $1,864.47
2006-11-14 00:00:00.000 $1,750.59
2006-11-15 00:00:00.000 $1,948.97
2006-11-16 00:00:00.000 $2,148.17
2006-11-17 00:00:00.000 $2,026.26
2006-11-18 00:00:00.000 $2,032.09
2006-11-19 00:00:00.000 NULL
2006-11-20 00:00:00.000 $1,897.51
2006-11-21 00:00:00.000 $1,771.48
2006-11-22 00:00:00.000 $1,896.66
2006-11-23 00:00:00.000 $2,024.81
2006-11-24 00:00:00.000 $1,882.29
2006-11-25 00:00:00.000 $1,976.44
2006-11-26 00:00:00.000 NULL
2006-11-27 00:00:00.000 $1,724.65
2006-11-28 00:00:00.000 $1,697.80
2006-11-29 00:00:00.000 $1,768.01
2006-11-30 00:00:00.000 $2,210.79
*/
[解决办法]
--生成一个日期列表再左关联销售表
select A.[date], 销售金额=isnull(B.销售金额, 0)
from
(
select [date]= '2006-11-01 '
union all
select '2006-11-02 '
union all
...
)A
left join 销售表 as B on day(A.[date])=B.销售日期
[解决办法]
create proc FormRecord
@s smalldatetime
as
declare @num int
declare @str varchar(1000)
declare @a table(a int,b varchar(100))
insert @a select 1 , '$1,722.97 '
union all select 2 , '$2,161.02 '
union all select 3 , '$2,090.73 '
union all select 4 , '$2,213.71 '
union all select 6 , '$1,759.49 '
union all select 7 , '$1,713.14 '
union all select 8 , '$1,701.17 '
union all select 9 , '$2,291.87 '
union all select 10 , '$2,040.68 '
union all select 11 , '$2,123.06 '
union all select 13 , '$1,864.47 '
union all select 14 , '$1,750.59 '
union all select 15 , '$1,948.97 '
union all select 16 , '$2,148.17 '
union all select 17 , '$2,026.26 '
union all select 18 , '$2,032.09 '
union all select 20 , '$1,897.51 '
union all select 21 , '$1,771.48 '
union all select 22 , '$1,896.66 '
union all select 23 , '$2,024.81 '
union all select 24 , '$1,882.29 '
union all select 25 , '$1,976.44 '
union all select 27 , '$1,724.65 '
union all select 28 , '$1,697.80 '
union all select 29 , '$1,768.01 '
union all select 30 , '$2,210.79 '
if object_id( 'yyy ') is not null
drop table yyy
set @num=datediff(day,@s,dateadd(m,1,@s))
set @str= 'select top ' +ltrim(@num) + ' id=identity(int,0,1),[date]=convert(varchar(20), ' ' ' ') into yyy from sysobjects '
exec(@str)
update yyy set [date]=ltrim(dateadd(day,id,@s))
select convert(varchar(10),cast([date] as datetime),120) [date],isnull(b,0) b from yyy a Left Join @a b On a.id+1=b.a
go
FormRecord '2006-11-01 '
