接到新任务：设计统计电子和磁电公司生产部产值分析报表。
眼下状况：
1.电子公司：取最新单位价格*入库数量
2.磁电公司：取最低价格*入库数量（实际取价的时候又没有取到最低价）
假设计算出来的结果和財务提供给生产部的报表有差异怎么办？给多 给少都不合适。
两个原则：1.合理原则 2.假设上级不允许，按领导意思办
依照当时的客户订单的不含税单位价格是最合理的，是多少就是多少
1.电子公司去客户订单的不含税单位价格
-- =============================================
-- Author: <David Gong>
-- Create date: <2014-06-17>
-- Description: <生产部KPI考核产值分析汇总>
-- =============================================
ALTER Procedure [dbo].[UP_Production]
(
@yearmonth as nvarchar(6)
)
as
begin
select isnull(b.类别,'合计') as 类别 ,入库数量, 产值 from(
select a.类别,cast(SUM(入库数量) as decimal(10,2)) as 入库数量,cast(SUM(入库数量*售价) as decimal(10,2)) as 产值 from (
Select TG004 品号,TG005 品名,TG006 规格 ,TG011 入库数量,TC008 as 币种,TC009 AS 汇率,MA101 税率,
case when LEFT(TG004,3)<>'203' THEN ISNULL(TD011/(1+MA101)*TC009,0)
else (MB057+MB058+MB059+MB060)*1.2 END AS 售价,
case when LEFT(TG004,3)='102' then 'Relay'
when LEFT(TG004,3)='103' then 'PCB'
when LEFT(TG004,3) in ('104','106','203') then 'BL'
when LEFT(TG004,3)='101' and CHARINDEX('ATM',TG006)>0 then 'TFT'
when LEFT(TG004,3)='101' and CHARINDEX('AQM',TG006)>0 then 'COG'
ELSE 'COB'
END 类别
from MOCTG INNER JOIN MOCTF ON TF001=TG001 AND TF002=TG002
left join MOCTA ON TG014=TA001 AND TG015=TA002
left join COPTD ON TA026=TD001 AND TA027=TD002 AND TA028=TD003
left JOIN COPTC ON TC001=TD001 AND TC002=TD002
left join COPMA on TC004=MA001
left join INVMB ON TG004=MB001
where left(TF003,6)=@yearmonth ) a
group by 类别,
类别 WITH ROLLUP) b
/*
;WITH cte as(
SELECT
ROW_NUMBER() OVER (PARTITION BY MB002 ORDER BY MB017 DESC) cnt,MB002 As 品号,MB004 币种,MB008 单位价格 FROM COPMB
)
select c.品号, c.品名, c.规格 ,c.入库数量,a.单位价格,b.币种,b.汇率,
case when LEFT(c.品号,3)<>'203' and b.币种<>'RMB' THEN a.单位价格*b.汇率
when LEFT(c.品号,3)<>'203' and b.币种='RMB' THEN a.单位价格/1.17
else (MB057+MB058+MB059+MB060)*1.2 END AS 售价,
case when LEFT(c.品号,3)='102' then 'Relay'
when LEFT(c.品号,3)='103' then 'PCB'
when LEFT(c.品号,3) in ('104','106','203') then 'BL'
when LEFT(c.品号,3)='101' and CHARINDEX('ATM',c.规格)>0 then 'TFT'
when LEFT(c.品号,3)='101' and CHARINDEX('AQM',c.规格)>0 then 'COG'
ELSE 'COB'
END 分类
into #Production
from (
Select TG004 品号,TG005 品名,TG006 规格 ,SUM(TG011) 入库数量
from MOCTG INNER JOIN MOCTF ON TF001=TG001 AND TF002=TG002
where left(TF003,6)=@yearmonth
GROUP BY TG004,TG005,TG006
) c
left join (SELECT * FROM cte WHERE cnt=1) a on a.品号=c.品号
left join (SELECT MG001 as 币种, MG004 as 汇率 FROM CMSMG WHERE left(MG002,6)=@yearmonth
UNION ALL
Select 'RMB',1.0 ) b ON a.币种=b.币种
left join INVMB on MB001=c.品号
select isnull(e.分类,'合计') as 类别,e.入库数量,e.产值 from (
select 分类,cast(SUM(入库数量) as decimal(10,2)) as 入库数量, cast(SUM(入库数量*售价) as decimal(10,2)) as 产值 from #Production
group by 分类,
分类 WITH ROLLUP) e
drop table #Production
*/
end2.磁电公司按合理原则跑出的结果比財务提供的多了40W。
领导说取最低价。
-- =============================================
-- Author: <David Gong>
-- Create date: <2014-06-16>
-- Description: <取最小价格>
-- =============================================
ALTER function [dbo].[GetMinPrice](
@ITEM AS VARCHAR(20),
@YEARMONTH AS VARCHAR(6)
)returns decimal(15,2)
as
begin
declare @Price as decimal(10,4)
select @Price=min(c.售价) from (
select MB002 As 品号,MB004 币种,MB008 单位价格 ,b.汇率,
case when MB004<>'RMB' THEN MB008*b.汇率
else MB008/1.17 end as 售价
FROM COPMB
left join (SELECT MG001 as 币种, MG004 as 汇率 FROM CMSMG WHERE left(MG002,6)=@YEARMONTH
UNION ALL
Select 'RMB',1.0 ) b ON MB004=b.币种
where MB002=@ITEM )c
return @Price
end
-- =============================================
-- Author: <David Gong>
-- Create date: <2014-06-17>
-- Description: <生产部KPI考核产值分析汇总>
-- =============================================
ALTER Procedure [dbo].[UP_Production]
(
@yearmonth as nvarchar(6)
)
as
begin
select isnull(b.类别,'合计') as 类别 ,入库数量, 产值 from(
select a.类别,cast(SUM(入库数量) as decimal(10,2)) as 入库数量,cast(SUM(入库数量*售价) as decimal(10,2)) as 产值 from (
Select TG004 品号,TG005 品名,TG006 规格 ,TG011 入库数量,TC008 as 币种,TC009 AS 汇率,MA101 税率,ZM.[dbo].[GetMinPrice](TG004,@yearmonth) 售价,
case when CHARINDEX('。生产100%检验',MOCTG.UDF01)>0 THEN '外协'
else REPLACE(MOCTG.UDF01,'，生产100%检验','') end as 类别
from MOCTG INNER JOIN MOCTF ON TF001=TG001 AND TF002=TG002
left join MOCTA ON TG014=TA001 AND TG015=TA002
left join COPTD ON TA026=TD001 AND TA027=TD002 AND TA028=TD003
left JOIN COPTC ON TC001=TD001 AND TC002=TD002
left join COPMA on TC004=MA001
where left(TF003,6)=@yearmonth ) a
group by 类别,
类别 WITH ROLLUP) b
end
比对前三个月財务提供给生产部的报表 。产值少了20W左右。
20W*0.17=3.4W
少了3.4W的奖金，预计他们也会对我不满了。。。