表 tableid 日期 仓库 费用
1 2011-01-01 1 3
2 2011-01-02 1 2
3 2011-06-01 1 7
4 2011-02-01 2 12
5 2011-02-05 2 1要取每个仓库,最后30天的费用和,这个最后30天并不是离今天的天数,是每个仓库最后一次发货的日期向前30天,
比如上面的结果集 查处一下结果仓库 费用
1 7
2 13
1 2011-01-01 1 3
2 2011-01-02 1 2
3 2011-06-01 1 7
4 2011-02-01 2 12
5 2011-02-05 2 1要取每个仓库,最后30天的费用和,这个最后30天并不是离今天的天数,是每个仓库最后一次发货的日期向前30天,
比如上面的结果集 查处一下结果仓库 费用
1 7
2 13
FROM TB A WHERE 日期>DATEADD(M,-1,(SELECT MAX(日期) FROM TB WHERE 仓库=A.仓库 )
select 仓库,费用 from tb
where 日期 in(select top 30 日期 from tb where 仓库=a.仓库 order by 日期 desc)
)t group by 仓库
where datediff(day,日期,(select max(日期) from tb where 仓库=a.仓库 group by 仓库))<=30
group by 仓库
create table tb(id int,riqi datetime,cangku int,feiyong int)
insert into tb
select 1,'2011-01-01',1,3 union all
select 2,'2011-01-02',1,2 union all
select 3,'2011-06-01',1,7 union all
select 4,'2011-02-01',2,12 union all
select 5,'2011-02-05',2,1
goselect cangku,sum(feiyong) fei
from tb t
where datediff(dd,riqi,(select max(riqi) from tb where cangku = t.cangku)) <= 30
group by cangkudrop table tb
/****************cangku fei
----------- -----------
1 7
2 13(2 行受影响)
SELECT 仓库,SUM(费用)
FROM TEST02 A WHERE 日期>DATEADD(M,-1,(SELECT MAX(日期) FROM TEST02 WHERE 仓库=A.仓库))
GROUP BY 仓库
insert into tb values (1, '2011-01-01' , 1 , 3)
insert into tb values (2 , '2011-01-02' , 1 , 2)
insert into tb values (3 , '2011-06-01' , 1 , 7)
insert into tb values (4 , '2011-02-01' , 2 , 12)
insert into tb values (5 , '2011-02-05' , 2 , 1)select a.仓库,SUM(a.费用) from tb a,(select 仓库,max(日期) M_日期 from tb
group by 仓库) b
where a.仓库=b.仓库
and DATEDIFF(dd,日期,M_日期)<=30
group by a.仓库仓库 费用
1 7
2 13
(select 仓库,日期=max(日期) from tb group by 仓库) b on a.仓库=b.仓库 and a.日期
between dateadd(d,-30,b.日期) and b.日期
group by a.仓库
仓库,sum(费用) from tb a
where
datediff(dd,日期,(select max(日期) from tb where 仓库=a.仓库 group by 仓库))<=30
group by
仓库
declare @table table (id int,日期 datetime,仓库 int,费用 int)
insert into @table
select 1,'2011-01-01',1,3 union all
select 2,'2011-01-02',1,2 union all
select 3,'2011-06-01',1,7 union all
select 4,'2011-02-01',2,12 union all
select 5,'2011-02-05',2,1select a.仓库,费用=sum(a.费用) from @table a left join (
select 仓库,dateadd(d,-30,max(日期)) as begintime,max(日期)
as endtime from @table group by 仓库) b on a.仓库=b.仓库
where a.日期 between b.begintime and b.endtime
group by a.仓库
/*
仓库 费用
----------- -----------
1 7
2 13
*/
create table tb(id int,日期 datetime,仓库 int,费用 int)
insert into tb select 34,'2011-03-10',1,150
insert into tb select 35,'2011-03-11',1,150
insert into tb select 36,'2011-03-12',1,150
insert into tb select 38,'2011-03-13',1,15
insert into tb select 39,'2011-03-15',1,18
insert into tb select 40,'2011-03-16',1,22 --这是1仓库的最后一天,最后三天和为55
insert into tb select 92,'2011-03-30',2,150
insert into tb select 94,'2011-04-01',2,150
insert into tb select 95,'2011-04-02',2,150
insert into tb select 96,'2011-04-03',2,28
insert into tb select 97,'2011-04-04',2,33
insert into tb select 99,'2011-04-06',2,12--这是2仓库的最后一天,最后三天的和为73
go
select 仓库,sum(费用)费用 from(
select 仓库,费用 from tb a
where 日期 in(
select top 3 日期 from tb where 仓库=a.仓库 order by 日期 desc)
--这里取 top 3 如要最后30天,则 top 30
)t group by 仓库
/*仓库 费用
----------- -----------
1 55
2 73(2 行受影响)*/
go
drop table tb
create table #tb1
(id int,日期 datetime, 仓库 int, 费用 int)
insert #tb1
select 1 ,'2011-01-01',1 ,3 union all
select 2 ,'2011-01-02',1 ,2 union all
select 3 ,'2011-06-01',1 ,7 union all
select 4 ,'2011-02-01',2 ,12 union all
select 5 ,'2011-02-05',2 ,1;with T as(select * ,max(日期)over(partition by 仓库) as maxDate from #tb1)
select 仓库,sum(费用) as 费用 from T where
日期 between dateadd(day,-30,maxDate) and maxDate group by 仓库仓库 费用
----------- -----------
1 7
2 13(2 row(s) affected)