一直提示列名FWorkshipID无效。代码如下：----------.汇总表(金额)************/
declare @strName varchar(100)
declare @strCode varchar(100)
set nocount on;
IF OBJECT_ID(N'tempdb.dbo.#temp1') IS NOT NULL
BEGIN
DROP TABLE #temp1
END
IF OBJECT_ID(N'tempdb.dbo.#StockClass') IS NOT NULL
BEGIN
DROP TABLE #StockClass
END
Create Table #StockClass (
FStockClassID int
)
begin
if exists(select 1 from Mf_StockClass where FStockClassID=255 and Upid=0)
begin
select @strCode=LevelCode from Mf_StockClass where FStockClassID=255
insert into #StockClass(FStockClassID)
select FStockClassID from Mf_StockClass with (nolock)
where left(LevelCode,4)=@strCode
end
end
;WITH tb AS (
SELECT d.FStockResID,d.FWorkShipID,
d.FWorkName as 仓库,g.FStClName as 物料一级分类,f.FStClName as 物料二级分类,d.FStockResID as id,c.FTaxPrice as 单价,d.FResCode as 物料编码,FResName as 物料名称,FResSpec as 规格型号,FUnitName as 单位,
sum(本期总入库) as 本期入库,sum(本期总出库) as 本期出库,sum(本期结存) as 本期结存,sum(上期结存) as 上期结存,
sum(本期报废) as 本期报废,SUM(本期调整) as 本期调整,isnull(c.FTaxPrice,0)*sum(本期结存) AS 结存金额 from IKD_Result.dbo.St_StockResCurrent d with (nolock) left JOIN
(SELECT a.FTaxPrice,a.FStockResID,a.FStockEnterDeID FROM (select FTaxPrice,FStockResID,FStockEnterDeID from Mf_StockEnterDe union all select FTaxPrice,FStockResID,FStockEnterDeID from IKD_Data.dbo.Mf_StockEnterDe) a inner join
( SELECT FStockResID,MAX(FStockEnterDeID) AS FStockEnterDeID FROM (SELECT FStockResID,FStockEnterDeID FROM Mf_StockEnterDe WHERE ISNULL(FTaxPrice,0)<>0 union all SELECT FStockResID,FStockEnterDeID FROM IKD_Data.dbo.Mf_StockEnterDe WHERE ISNULL(FTaxPrice,0)<>0)e GROUP BY FStockResID)b
on a.FStockEnterDeID=b.FStockEnterDeID) c on d.FStockResID=c.FStockResID
left join Mf_StockClass f on f.FStockClassID=d.FStockClassID
left join Mf_StockClass g on g.FStockClassID=f.upid
where d.FResType=1
and d.FStatus=1
and (d.FWorkShipID=0 or 0=0)
and (d.FStockClassID in (select FStockClassID from #StockClass with (nolock)) )
and (d.FStockResID=0 or 0=0)
group BY d.FStockResID,d.FWorkShipID,
d.FWorkName,g.FStClName,f.FStClName,FResCode,FResName,FResSpec,FUnitName,d.FStockResID,c.FTaxPrice
),
tb_jiecun AS (
SELECT tb.FStockResID,tb.FWorkShipID, tb.仓库,tb.物料编码,tb.物料名称,tb.规格型号,tb.本期结存,tb.结存金额,ISNULL(tb.单价,0)单价 FROM tb WHERE tb.本期结存>0
),
tb_hetong AS (
SELECT b.FStockResID,b.FPrice FROM Si_Contract a LEFT JOIN Si_ContractDe b ON a.FContractID=b.FContractID
LEFT JOIN dbo.Mf_Supply c ON c.FSupplyID=a.FSupplyID
WHERE a.FType=2 AND a.FEnable=1
GROUP BY b.FStockResID,b.FPrice
)
SELECT tb_jiecun.*,CASE WHEN isnull(tb_jiecun.单价,0)=0 THEN tb_hetong.FPrice*1.13 WHEN isnull(tb_jiecun.单价,0)<>0 THEN tb_jiecun.单价 ELSE 0 END fprice
into #temp1
FROM tb_jiecun LEFT JOIN tb_hetong ON tb_hetong.FStockResID = tb_jiecun.FStockResID WHERE tb_jiecun.仓库 NOT LIKE '%夹具%'
and 仓库 in ('${仓库}')
;WITH tb AS (
SELECT a.单价 ,
CASE WHEN ISNULL(a.结存金额,0)=0 AND a.fprice<>0 THEN a.fprice*a.本期结存 WHEN ISNULL(a.结存金额,0)<>0 THEN a.结存金额 ELSE 0 END jiecun_amt ,
a.本期结存 ,
a.规格型号 ,
a.物料名称 ,
a.物料编码 ,
a.仓库 ,a.FWorkShipID,
a.FStockResID ,
a.fprice
FROM #temp1 a
),
tb_replace AS (
SELECT FStockResID,FNStockResID,FWorkshipID FROM dbo.St_Replace
),
tb_merge_id AS (
SELECT tb.*,tb_replace.FNStockResID FROM tb LEFT JOIN tb_replace ON tb.FStockResID=tb_replace.FStockResID AND tb.FWorkShipID=tb_replace.FWorkshipID
WHERE tb_replace.FNStockResID IS NOT NULL
UNION
SELECT tb.*,tb_replace.FNStockResID FROM tb LEFT JOIN tb_replace ON tb.FStockResID=tb_replace.FStockResID AND tb.FWorkShipID=tb_replace.FWorkshipID
WHERE tb_replace.FNStockResID IS NULL
),
tb_idnew AS (
SELECT CASE WHEN a.FNStockResID IS NOT NULL
THEN a.FNStockResID WHEN a.FNStockResID IS NULL
THEN a.FStockResID ELSE 0 END FStockResID,a.本期结存,a.jiecun_amt ,a.fprice,a.仓库
FROM tb_merge_id a WHERE 1=1
),
tb_newoldfor AS (
SELECT tb_idnew.FStockResID,a.FResCode,a.FResName,tb_idnew.本期结存,tb_idnew.jiecun_amt,tb_idnew.仓库
FROM tb_idnew LEFT JOIN mf_stockres a ON a.FStockResID=tb_idnew.FStockResID
WHERE 1=1
)
SELECT a.FStockResID,b.FResName AS 物料名称,b.FResSpec AS 规格型号,
SUM(a.本期结存)本期结存,SUM(a.jiecun_amt)jiecun_amt,
SUM(a.jiecun_amt)/SUM(a.本期结存) AS fprice,
a.仓库
FROM tb_newoldfor a
LEFT JOIN dbo.Mf_StockRes b ON a.FStockResID=b.FStockResID
WHERE 1=1
GROUP BY a.FStockResID,b.FResName,b.FResSpec,
a.仓库
