select sum(费用) from tb where datediff(month,日期,getdate())=1
--上两个月 select sum(费用) from tb where datediff(month,日期,getdate())<2 and datediff(month,日期,getdate())>0
select isnull(sum(费用),0.0) from tb where 日期 between convert(varchar(8),dateadd(m,-1,getdate()),120)+'01' and dateadd(d,-1,convert(varchar(8),getdate(),120)+'01' )要向上类推,要用循环或递归.
DECLARE @Date DATETIME; -- 先取本月之前的最大日期 SET @Date=(SELECT MAX(日期) FROM TB WHERE 日期<DATEADD(M,DATEDIFF(M,0,GETDATE()),0)) -- 根据本月之前的最大日期 获取当月费用和 SELECT SUM(费用) FROM TB WHERE DATEDIFF(M,日期,@Date)=0
select * from tb where convert(varchar(7),Createddate,120 ) = (select top 1 convert(varchar(7),Createddate,120 ) as mon from tb where convert(varchar(7),Createddate,120 )<convert(varchar(7),getdate(),120 ) group by convert(varchar(7),Createddate,120 ) order by convert(varchar(7),Createddate,120 ) desc)
create table #tb (日期 datetime,费用 int) insert #tb select '2011-04-06',10 union all select '2011-04-07',11 union all select '2011-04-08',12 union all select '2011-04-09',13 union all select '2011-04-10',14 union all select '2011-04-11',15select top 1 convert(varchar(7),日期,120) 日期,sum(费用)over(partition by convert(varchar(7),日期,120)) 总和 from #tb where convert(varchar(7),日期,120)<convert(varchar(7),Getdate(),120) order by 日期 desc日期 总和 ------- ----------- 2011-04 75(1 row(s) affected)
select sum(费用) from tb where datediff(month,日期,getdate())<2
and datediff(month,日期,getdate())>0
select isnull(sum(费用),0.0) from tb
where 日期 between convert(varchar(8),dateadd(m,-1,getdate()),120)+'01'
and dateadd(d,-1,convert(varchar(8),getdate(),120)+'01' )要向上类推,要用循环或递归.
DECLARE @Date DATETIME;
-- 先取本月之前的最大日期
SET @Date=(SELECT MAX(日期) FROM TB WHERE 日期<DATEADD(M,DATEDIFF(M,0,GETDATE()),0))
-- 根据本月之前的最大日期 获取当月费用和
SELECT SUM(费用) FROM TB WHERE DATEDIFF(M,日期,@Date)=0
select * from tb where convert(varchar(7),Createddate,120 ) =
(select top 1 convert(varchar(7),Createddate,120 )
as mon from tb where convert(varchar(7),Createddate,120 )<convert(varchar(7),getdate(),120 ) group by convert(varchar(7),Createddate,120 )
order by convert(varchar(7),Createddate,120 ) desc)
create table #tb
(日期 datetime,费用 int)
insert #tb
select '2011-04-06',10 union all
select '2011-04-07',11 union all
select '2011-04-08',12 union all
select '2011-04-09',13 union all
select '2011-04-10',14 union all
select '2011-04-11',15select top 1 convert(varchar(7),日期,120) 日期,sum(费用)over(partition by convert(varchar(7),日期,120)) 总和 from #tb
where convert(varchar(7),日期,120)<convert(varchar(7),Getdate(),120) order by 日期 desc日期 总和
------- -----------
2011-04 75(1 row(s) affected)