产品兑现率的统计

[zjb_619]

产品类型 产品名称 单价 排产时间 排产数量 完成时间 完成数量
a a101 100 2009-3-1 20 2009-4-5 20
a a102 50 2009-2-1 50 2009-3-5 30
b b101 200 2009-2-5 50 2009-2-28 50
b b102 100 2009-3-5 100 2009-5-10 20
b b103 50 2009-3-15 20 2009-6-25 20
c c101 200 2009-2-10 40 2009-4-5 20
c c102 300 2009-4-10 50 null null
c c103 100 2009-5-21 20 null null

现在有如上一个表，想要按月统计时间段之内的产品兑现率和金额兑现率，就是：完成数量/排产数量，金额兑现率就是乘上单价。
例如：现在想查3～5月的产品兑现率，就是：3、4、5三个月的完成数量和/3、4、5三个月的排产数量和，统计出来的表是按产品类型分类的。
如下：
1 2 3 4 5 6 7 8 9 10 11 12
a

b

c

这个怎么做呢？？我想了个代码，但是太麻烦了。有人能指教一下么？？？？？？？？？？？？

[三楼の郎]

select a.[产品类型],b.[完成数量]/b.[排产数量] as 1,c.[完成数量]/c.[排产数量] as 2,d.[完成数量]/d.[排产数量] as 3,e.[完成数量]/e.[排产数量] as 4,f.[完成数量]/f.[排产数量] as 5,g.[完成数量]/g.[排产数量] as 6,h.[完成数量]/h.[排产数量] as 7,i.[完成数量]/i.[排产数量] as 8,j.[完成数量]/j.[排产数量] as 9,k.[完成数量]/k.[排产数量] as 10,l.[完成数量]/l.[排产数量] as 11,m.[完成数量]/m.[排产数量] as 12 from
(select distinct [产品类型] from tablename) as a 
left join (select [产品类型],sum(排产数量) as [排产数量],sum(完成数量) as [完成数量] from tablename group by [产品类型] where datediff(month,[排产时间],'2009-01-01')=0) as b on a.[产品类型]=b.[产品类型] 
left join (select [产品类型],sum(排产数量) as [排产数量],sum(完成数量) as [完成数量] from tablename group by [产品类型] where datediff(month,[排产时间],'2009-02-01')=0) as c on a.[产品类型]=c.[产品类型]
left join (select [产品类型],sum(排产数量) as [排产数量],sum(完成数量) as [完成数量] from tablename group by [产品类型] where datediff(month,[排产时间],'2009-03-01')=0) as d on a.[产品类型]=d.[产品类型]
left join (select [产品类型],sum(排产数量) as [排产数量],sum(完成数量) as [完成数量] from tablename group by [产品类型] where datediff(month,[排产时间],'2009-04-01')=0) as e on a.[产品类型]=e.[产品类型]
left join (select [产品类型],sum(排产数量) as [排产数量],sum(完成数量) as [完成数量] from tablename group by [产品类型] where datediff(month,[排产时间],'2009-05-01')=0) as f on a.[产品类型]=f.[产品类型]
left join (select [产品类型],sum(排产数量) as [排产数量],sum(完成数量) as [完成数量] from tablename group by [产品类型] where datediff(month,[排产时间],'2009-06-01')=0) as g on a.[产品类型]=g.[产品类型]
left join (select [产品类型],sum(排产数量) as [排产数量],sum(完成数量) as [完成数量] from tablename group by [产品类型] where datediff(month,[排产时间],'2009-07-01')=0) as h on a.[产品类型]=h.[产品类型]
left join (select [产品类型],sum(排产数量) as [排产数量],sum(完成数量) as [完成数量] from tablename group by [产品类型] where datediff(month,[排产时间],'2009-08-01')=0) as i on a.[产品类型]=i.[产品类型]
left join (select [产品类型],sum(排产数量) as [排产数量],sum(完成数量) as [完成数量] from tablename group by [产品类型] where datediff(month,[排产时间],'2009-09-01')=0) as j on a.[产品类型]=j.[产品类型]
left join (select [产品类型],sum(排产数量) as [排产数量],sum(完成数量) as [完成数量] from tablename group by [产品类型] where datediff(month,[排产时间],'2009-10-01')=0) as k on a.[产品类型]=k.[产品类型]
left join (select [产品类型],sum(排产数量) as [排产数量],sum(完成数量) as [完成数量] from tablename group by [产品类型] where datediff(month,[排产时间],'2009-11-01')=0) as l on a.[产品类型]=l.[产品类型]
left join (select [产品类型],sum(排产数量) as [排产数量],sum(完成数量) as [完成数量] from tablename group by [产品类型] where datediff(month,[排产时间],'2009-12-01')=0) as m on a.[产品类型]=m.[产品类型]

手写的，没调试过，Null值可能会出问题

[三楼の郎]

Sorry，有错误，需要把Group by [产品类型] 和 where 条件 的位置互换一下！