日期:2014-05-18 浏览次数:20578 次
select id=row_number() over (order by getdate()),a.工厂,a.产成品,max(结束日) as 最大结束日, '2011-07-01' as 截止日,datediff(mm,max(结束日),'2011-07-01') as 计费月,应收单价, sum(datediff(mm,结束日,'2011-07-01')*应收单价) as 应收合计 from tb a left join (select 工厂,产成品 from tb where 结束日 >= '2011-07-01') b on a.工厂 = b.工厂 and a.产成品 = b.产成品 where b.工厂 is null and b.产成品 is null group by a.工厂,a.产成品,应收单价
------解决方案--------------------
/*
原始数据表:
id 工厂 产成品 开始日 结束日 计费月 应收单价 应收合计
1 工厂1 产成品1 2011-1-1 2011-2-1 1 1 1
2 工厂1 产成品1 2011-2-1 2011-4-1 2 1 2
3 工厂1 产成品1 2011-4-1 2011-5-1 1 1 1
4 工厂1 产成品1 2011-5-1 2011-6-1 1 1 1
5 工厂1 产成品1 2011-6-1 2011-8-1 2 1 2
6 工厂1 产成品2 2011-1-1 2011-3-1 2 2 4
7 工厂1 产成品2 2011-3-1 2011-4-1 1 2 2
8 工厂1 产成品2 2011-4-1 2011-5-1 1 2 2
9 工厂2 产成品1 2011-1-1 2011-2-1 1 1 1
10 工厂2 产成品1 2011-2-1 2011-3-1 1 1 1
要求结果,截止2011-7-1。结束日大于2011-7-1的不显示,
结束日小于2011-7-1的取出结束日并计算月数差乘以单价
得出应收再合计。
id 工厂 产成品 最大结束日 截止日 计费月 应收单价 应收合计
1 工厂1 产成品2 2011-5-1 2011-7-1 2 2 4
2 工厂2 产成品1 2011-3-1 2011-7-1 4 1 4
合计 8
*/
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
编号 int identity(1,1),
工厂 varchar(10),
产成品 varchar(10),
开始日 datetime,
结束日 datetime,
计费月 varchar(5),
应收单价 varchar(10),
应收合计 varchar(10)
)
go
insert tbl
select '工厂1','产成品1','2011-1-1','2011-2-1','1','1','1' union all
select '工厂1','产成品1','2011-2-1','2011-4-1','2','1','2' union all
select '工厂1','产成品1','2011-4-1','2011-5-1','1','1','1' union all
select '工厂1','产成品1','2011-5-1','2011-6-1','1','1','1' union all
select '工厂1','产成品1','2011-6-1','2011-8-1','2','1','2' union all
select '工厂1','产成品2','2011-1-1','2011-3-1','2','2','4' union all
select '工厂1','产成品2','2011-3-1','2011-4-1','1','2','2' union all
select '工厂1','产成品2','2011-4-1','2011-5-1','1','2','2' union all
select '工厂2','产成品1','2011-1-1','2011-2-1','1','1','1' union all
select '工厂2','产成品1','2011-2-1','2011-3-1','1','1','1'
select *from tbl
/*
要求结果,截止2011-7-1。结束日大于2011-7-1的不显示,
结束日小于2011-7-1的取出结束日并计算月数差乘以单价
得出应收再合计
*/
--id 工厂 产成品 最大结束日 截止日 计费月 应收单价 应收合计
;with T
as
(
select row_number()over(partition by 工厂,产成品 order by MAX(结束日) desc) as num,
编号,工厂,产成品,最大结束日=MAX(结束日),结束日='2011-7-1',
计费月=DATEDIFF(MM,MAX(结束日),'2011-7-1'),
应收单价,应收合计=DATEDIFF(MM,MAX(结束日),'2011-7-1')*cast(应收单价 as numeric(5,2))
from tbl where 结束日<='2011-7-1'
group by 编号,工厂,产成品,应收单价,结束日,应收单价
)
select 编号,工厂,产成品,最大结束日,结束日,计费月,应收单价,应收合计
from T where num=1
union all
select null,'合','计',null,null,null,':',SUM(cast(应收合计 as numeric(5,2))) from T
where num=1
/*
编号 工厂 产成品 最大结束日 结束日 计费月 应收单价 应收合计
4 工厂1 产成品1 2011-06-01 00:00:00.000 2011-7-1 1 1 1.00
8 工厂1 产成品2 2011-05-01 00:00:00.000 2011-7-1 2 2 4.00
10 工厂2 产成品1 2011-03-01 00:00:00.000 2011-7-1 4 1 4.00
NULL 合 计 NULL NULL NULL : 9.00
*/