初学者：单表账龄分析指定日期前的应收账款合计sql适用sql和access

菜鸟：单表账龄分析指定日期前的应收账款合计sql适用sql和access
原始数据表：
id工厂产成品开始日结束日计费月应收单价应收合计
1工厂1产成品12011-1-12011-2-1111
2工厂1产成品12011-2-12011-4-1212
3工厂1产成品12011-4-12011-5-1111
4工厂1产成品12011-5-12011-6-1111
5工厂1产成品12011-6-12011-8-1212
6工厂1产成品22011-1-12011-3-1224
7工厂1产成品22011-3-12011-4-1122
8工厂1产成品22011-4-12011-5-1122
9工厂2产成品12011-1-12011-2-1111
10工厂2产成品12011-2-12011-3-1111
要求结果，截止2011-7-1。结束日大于2011-7-1的不显示，结束日小于2011-7-1的取出结束日并计算月数差乘以单价得出应收再合计。
id工厂产成品最大结束日 截止日计费月应收单价应收合计
1工厂1产成品22011-5-12011-7-1224
2工厂2产成品12011-3-12011-7-1414
合计8

[解决办法]

SQL code

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 nullgroup 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产成品12011-06-01 00:00:00.0002011-7-1111.00
8工厂1产成品22011-05-01 00:00:00.0002011-7-1224.00
10工厂2产成品12011-03-01 00:00:00.0002011-7-1414.00
NULL合计NULLNULLNULL:9.00
*/