来自:http://blog.csdn.net/xys_777/article/details/5681208
得到连续日期需要借助一个有连续序号的表,参考如何得到连续序号
--〉生成连续日期的方法
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
GO
create table #t(id int identity,Dt varchar(10))
go
declare @starttime datetime,@endtime datetime
set @starttime = '2010-5-01'
set @endtime ='2010-5-31'
insert #t
select convert(varchar(10),dateadd(day,number,@starttime),120) dt
from master..spt_values
where type='P' and number between 0 and datediff(day,@starttime,@endtime)
--结果
select * from #t
/*
id Dt
----------- ----------
1 2010-05-01
2 2010-05-02
3 2010-05-03
4 2010-05-04
5 2010-05-05
6 2010-05-06
7 2010-05-07
8 2010-05-08
9 2010-05-09
10 2010-05-10
11 2010-05-11
12 2010-05-12
13 2010-05-13
14 2010-05-14
15 2010-05-15
16 2010-05-16
17 2010-05-17
18 2010-05-18
19 2010-05-19
20 2010-05-20
21 2010-05-21
22 2010-05-22
23 2010-05-23
24 2010-05-24
25 2010-05-25
26 2010-05-26
27 2010-05-27
28 2010-05-28
29 2010-05-29
30 2010-05-30
31 2010-05-31
(31 行受影响)
*/
实际例子:
原帖:
http://topic.csdn.net/u/20100619/18/3b4d60f2-b477-414f-adec-569ccee3fec6.html
http://topic.csdn.net/u/20100619/15/367ed306-b8ea-4b2b-9614-2acd64c0e07e.html
根据开始时间,结束时间 生成连续的时间
然后在用这个连续的时间去比较表里的数据
create table TZ_Money
(
Id int identity ( 1 , 1 ) primary key ,
Uid varchar ( 8 ) not null , -- 用户ID
Income numeric( 10 , 2 ) not null , -- 收入
Expenditure numeric( 10 , 2 ) not null , -- 支出
Balance numeric( 10 , 2 ) not null , -- 余额
[ Time ] datetime not null , -- 日期(天为单位)
LastTime datetime null -- 最后时间
)
go
set identity_insert TZ_Money on
insert TZ_Money (id,uid,Income,Expenditure,Balance,Time)
select 1 , ' abc1 ' , 1.00 , 5.00 , 96 , ' 2010-06-7 12:12:12 ' union all
select 2 , ' abc1 ' , 3.00 , 4.00 , 95 , ' 2010-06-7 21:12:45 ' union all
select 3 , ' abc1 ' , 4.00 , 50.00 , 49 , ' 2010-08-7 09:45:59 '
go
if object_id ( ' p_test1 ' ) is not null drop proc p_test1
go
create proc p_test1 @starttime datetime , @endtime datetime , @type nvarchar ( 4 ) = ' 年 ' , @uid varchar ( 8 )
as
begin
declare @sql varchar ( 8000 ), @w varchar ( 1000 ), @g varchar ( 1000 )
create table #t(id int identity ,D varchar ( 10 ))
set @sql = ' select isnull(sum(Income),0.00) 收入,isnull(sum(Expenditure),0.00)支出, '
if @type = ' 年 '
begin
insert #t
select convert ( varchar ( 4 ), dateadd ( year , number , @starttime ), 120 )
from master..spt_values
where type = ' P ' and number between 0 and datediff ( year , @starttime , @endtime )
set @sql = @sql + ' isnull((select top 1 Balance from TZ_Money where convert(varchar(4),[Time],120)<=a.d and Balance is not null order by time desc),0.00) 余额,a.d as [time] '
set @sql = @sql + ' from #t a left join TZ_Money t on a.d=convert(varchar(4),t.[Time],120) '
end
if @type = ' 月 '
begin
insert #t
select convert ( varchar ( 7 ), dateadd ( month , number , @starttime ), 120 )
from master..spt_values
where type = ' P ' and number between 0 and datediff ( month , @starttime , @endtime )
set @sql = @sql + ' isnull((select top 1 Balance from TZ_Money where convert(varchar(7),[Time],120)<=a.d and Balance is not null order by time desc),0.00) 余额,a.d as [time] '
set @sql = @sql + ' from #t a left join TZ_Money t on a.d=convert(varchar(7),t.[Time],120) '
end
if @type = ' 日 '
begin
insert #t
select convert ( varchar ( 10 ), dateadd ( day , number , @starttime ), 120 )
from master..spt_values
where type = ' P ' and number between 0 and datediff ( day , @starttime , @endtime )
set @sql = @sql + ' isnull((select top 1 Balance from TZ_Money where convert(varchar(10),[Time],120)<=a.d and Balance is not null order by time desc),0.00) 余额,a.d as [time] '
set @sql = @sql + ' from #t a left join TZ_Money t on a.d=convert(varchar(10),t.[Time],120) '
end
set @w = ''
if @starttime <> '' and @starttime is not null
set @w = ' and datediff(d, ''' + convert ( varchar ( 10 ), @starttime , 120 ) + ''' ,[Time])>=0 '
if @endtime <> '' and @starttime is not null
set @w = @w + ' and datediff(d, ''' + convert ( varchar ( 10 ), @endtime , 120 ) + ''' ,[Time])<=0 '
if @uid <> '' and @uid is not null
set @w = @w + ' Uid= ''' + @uid + ''''
set @g = ' group by a.d '
exec ( @sql + @w + @g )
end
go
p_test1 ' 2010-5-01 ' , ' 2010-9-12 ' , ' 年 ' , ''
go
p_test1 ' 2010-5-01 ' , ' 2010-9-12 ' , ' 月 ' , ''
go
p_test1 ' 2010-5-01 ' , ' 2010-9-12 ' , ' 日 ' , ''
/*
(所影响的行数为 3 行)
(所影响的行数为 1 行)
收 入 支 出 余额 time
---------------------------------------- ---------------------------------------- ------------ ----------
8.00 59.00 49.00 2010
(所影响的行数为 1 行)
(所影响的行数为 5 行)
收 入 支 出 余额 time
---------------------------------------- ---------------------------------------- ------------ ----------
.00 .00 .00 2010-05
4.00 9.00 95.00 2010-06
.00 .00 95.00 2010-07
4.00 50.00 49.00 2010-08
.00 .00 49.00 2010-09
(所影响的行数为 5 行)
警告: 聚合或其他 SET 操作消除了空值。
(所影响的行数为 135 行)
收 入 支 出 余额 time
---------------------------------------- ---------------------------------------- ------------ ----------
.00 .00 .00 2010-05-01
.00 .00 .00 2010-05-02
.00 .00 .00 2010-05-03
.00 .00 .00 2010-05-04
......
.00 .00 .00 2010-05-28
.00 .00 .00 2010-05-29
.00 .00 .00 2010-05-30
.00 .00 .00 2010-05-31
.00 .00 .00 2010-06-01
.00 .00 .00 2010-06-02
.00 .00 .00 2010-06-03
.00 .00 .00 2010-06-04
.00 .00 .00 2010-06-05
.00 .00 .00 2010-06-06
4.00 9.00 95.00 2010-06-07
.00 .00 95.00 2010-06-08
.00 .00 95.00 2010-06-09
.00 .00 95.00 2010-06-10
.00 .00 95.00 2010-06-11
.00 .00 95.00 2010-06-12
.00 .00 95.00 2010-06-13
.00 .00 95.00 2010-06-14
.00 .00 95.00 2010-06-15
.00 .00 95.00 2010-06-16
.00 .00 95.00 2010-06-17
.00 .00 95.00 2010-06-18
.00 .00 95.00 2010-06-19
.00 .00 95.00 2010-06-20
.00 .00 95.00 2010-06-21
.00 .00 95.00 2010-06-22
.00 .00 95.00 2010-06-23
.00 .00 95.00 2010-06-24
.......
.00 .00 95.00 2010-08-01
.00 .00 95.00 2010-08-02
.00 .00 95.00 2010-08-03
.00 .00 95.00 2010-08-04
.00 .00 95.00 2010-08-05
.00 .00 95.00 2010-08-06
4.00 50.00 49.00 2010-08-07
.00 .00 49.00 2010-08-08
.00 .00 49.00 2010-08-09
.00 .00 49.00 2010-08-10
.....
.00 .00 49.00 2010-09-06
.00 .00 49.00 2010-09-07
.00 .00 49.00 2010-09-08
.00 .00 49.00 2010-09-09
.00 .00 49.00 2010-09-10
.00 .00 49.00 2010-09-11
.00 .00 49.00 2010-09-12
(所影响的行数为 135 行)
警告: 聚合或其他 SET 操作消除了空值。
*/