表:姓名,金额,日期,
现在想建一个存贮过程:有两个变量@T1,@T2,两个日期,
然后求出从T1到T2的上一个月的金额总量,及T2所属月份的1号到T2之间的总金额.
比如T1=2011-03-05,T2=2011-05-09,
那就就查出从2011-03-05到2011-04-30之间的总金额及从2011-05-01到2011-05-09之间的总金额.
现在想建一个存贮过程:有两个变量@T1,@T2,两个日期,
然后求出从T1到T2的上一个月的金额总量,及T2所属月份的1号到T2之间的总金额.
比如T1=2011-03-05,T2=2011-05-09,
那就就查出从2011-03-05到2011-04-30之间的总金额及从2011-05-01到2011-05-09之间的总金额.
取当月第一天
select dateadd(d,-datepart(d,@date),@date)
上月最后一天
declare @T1 datetime,@T2 datetime set @T1 = '2011-03-05'
set @T2 = '2011-05-09'
select @T1,cast( (CONVERT(varchar(7), @T2, 120 ) + '-01') as datetime) select cast( (CONVERT(varchar(7), @T2, 120 ) + '-01') as datetime),@T2
取 < 号 和 >= 号就可以了
select sum(金额) from tb
where 日期 between @T1 and dateadd(day,-1,DATEADD(mm, DATEDIFF(mm,0,@T2), 0))
--T2所属月份的1号到T2之间的总金额
select sum(金额) from tb
where 日期 between dateadd(mm,datediff(mm,0,@T2),0) and @T2
create proc get_all(@start datetime,@end datetime)
as
;with cte as
(
select convert(varchar(7),dateadd(mm,number,@start),120) as yymm
from master..spt_values
where [type] = 'p' and number between 0 and datediff(mm,@start,@end)
)select a.yymm,sum(b.total) as total
from cte a left join tb b on a.yymm = convert(varchar(7),b.[日期],120)
group by a.yymm
go/*下边是简单的测试*/
create proc get_all(@start datetime,@end datetime)
as
;with cte as
(
select convert(varchar(7),dateadd(mm,number,@start),120) as yymm
from master..spt_values
where [type] = 'p' and number between 0 and datediff(mm,@start,@end)
)select * from cte
goexec get_all '2010-05-01','2011-02-19'drop proc get_all/*
yymm
-------
2010-05
2010-06
2010-07
2010-08
2010-09
2010-10
2010-11
2010-12
2011-01
2011-02(10 行受影响)
*/
create proc get_all(@start datetime,@end datetime)
as
begin
declare @st datetime
declare @et datetime
select @et = convert(datetime,convert(varchar(8),@end,120) + '01')
select @st = dateadd(dd,-1,@et)
print convert(varchar(10),@et,120) --@end所在月份之初
print convert(varchar(10),@st,120) --@end所在月份上一月之末
end
goexec get_all '2010-05-01','2011-02-19'drop proc get_all/*
2011-02-01
2011-01-31*/