27,579
社区成员
发帖
与我相关
我的任务
分享
select v_bom_parent_rpt.InvCode as 母件编码,
Inventory.cInvName as 母件名称,
Inventory.cInvStd as 母件型号,
PurSettleVouchs.cInvCode as 子件编码,
v1.cInvName as 子件名称,v1.cInvStd as 子件型号,
v1.cVenCode as 供应商代码,
PurSettleVouch.dsvdate as 结算时间,
PurSettleVouchs.iSVCost/0.83 as 单价,
v_bom_opcomponent_rpt.BaseQtyN as 基本用量,
PurSettleVouchs.iSVCost/0.83*v_bom_opcomponent_rpt.BaseQtyN as 小计
into #temp
from Inventory RIGHT JOIN (v_bom_parent_rpt LEFT JOIN (v_bom_opcomponent_rpt LEFT JOIN (PurSettleVouch RIGHT JOIN (PurSettleVouchs LEFT JOIN Inventory v1 on PurSettleVouchs.cInvCode=v1.cInvCode) on PurSettleVouch.PSVID=PurSettleVouchs.PSVID) on v_bom_opcomponent_rpt.InvCode=PurSettleVouchs.cInvCode) on v_bom_parent_rpt.BomId=v_bom_opcomponent_rpt.BomId) on Inventory.cInvCode=v_bom_parent_rpt.InvCode
select * from #temp a
where not exists(select 1 from #temp where 母件编码=a.母件编码 and 子件编码=a.子件编码 and 供应商代码=a.供应商代码 and 结算时间>a.结算时间)
select v_bom_parent_rpt.InvCode as 母件编码,
Inventory.cInvName as 母件名称,
Inventory.cInvStd as 母件型号,
PurSettleVouchs.cInvCode as 子件编码,
v1.cInvName as 子件名称,v1.cInvStd as 子件型号,
v1.cVenCode as 供应商代码,
PurSettleVouch.dsvdate as 结算时间,
PurSettleVouchs.iSVCost/0.83 as 单价,
v_bom_opcomponent_rpt.BaseQtyN as 基本用量,
PurSettleVouchs.iSVCost/0.83*v_bom_opcomponent_rpt.BaseQtyN as 小计
from Inventory RIGHT JOIN (v_bom_parent_rpt LEFT JOIN (v_bom_opcomponent_rpt LEFT JOIN (PurSettleVouch RIGHT JOIN (PurSettleVouchs LEFT JOIN Inventory v1 on PurSettleVouchs.cInvCode=v1.cInvCode) on PurSettleVouch.PSVID=PurSettleVouchs.PSVID) on v_bom_opcomponent_rpt.InvCode=PurSettleVouchs.cInvCode) on v_bom_parent_rpt.BomId=v_bom_opcomponent_rpt.BomId) on Inventory.cInvCode=v_bom_parent_rpt.InvCode
WHERE NOT EXISTS( -- 过滤条件
SELECT * FROM(
select
PurSettleVouchs.cInvCode,
PurSettleVouch.dsvdate
from Inventory RIGHT JOIN (v_bom_parent_rpt LEFT JOIN (v_bom_opcomponent_rpt LEFT JOIN (PurSettleVouch RIGHT JOIN (PurSettleVouchs LEFT JOIN Inventory v1 on PurSettleVouchs.cInvCode=v1.cInvCode) on PurSettleVouch.PSVID=PurSettleVouchs.PSVID) on v_bom_opcomponent_rpt.InvCode=PurSettleVouchs.cInvCode) on v_bom_parent_rpt.BomId=v_bom_opcomponent_rpt.BomId) on Inventory.cInvCode=v_bom_parent_rpt.InvCode
) AA
WHERE AA.cInvCode = PurSettleVouchs.cInvCode
AND AA.dsvdate > PurSettleVouch.dsvdate)
order by v_bom_parent_rpt.InvCode
select v_bom_parent_rpt.InvCode as 母件编码,
Inventory.cInvName as 母件名称,
Inventory.cInvStd as 母件型号,
PurSettleVouchs.cInvCode as 子件编码,
v1.cInvName as 子件名称,v1.cInvStd as 子件型号,
v1.cVenCode as 供应商代码,
PurSettleVouch.dsvdate as 结算时间,
PurSettleVouchs.iSVCost/0.83 as 单价,
v_bom_opcomponent_rpt.BaseQtyN as 基本用量,
PurSettleVouchs.iSVCost/0.83*v_bom_opcomponent_rpt.BaseQtyN as 小计
from Inventory RIGHT JOIN (v_bom_parent_rpt LEFT JOIN (v_bom_opcomponent_rpt LEFT JOIN (PurSettleVouch RIGHT JOIN (PurSettleVouchs LEFT JOIN Inventory v1 on PurSettleVouchs.cInvCode=v1.cInvCode) on PurSettleVouch.PSVID=PurSettleVouchs.PSVID) on v_bom_opcomponent_rpt.InvCode=PurSettleVouchs.cInvCode) on v_bom_parent_rpt.BomId=v_bom_opcomponent_rpt.BomId) on Inventory.cInvCode=v_bom_parent_rpt.InvCode
WHERE NOT EXISTS( -- 过滤条件
SELECT * FROM PurSettleVouchs AA
WHERE AA.cInvCode = PurSettleVouchs.cInvCode
AND AA.dsvdate > PurSettleVouchs.dsvdate
AND AA.PSVID=PurSettleVouchs.PSVID)
order by v_bom_parent_rpt.InvCode
select v_bom_parent_rpt.InvCode as 母件编码,
Inventory.cInvName as 母件名称,
Inventory.cInvStd as 母件型号,
PurSettleVouchs.cInvCode as 子件编码,
v1.cInvName as 子件名称,v1.cInvStd as 子件型号,
v1.cVenCode as 供应商代码,
PurSettleVouch.dsvdate as 结算时间,
PurSettleVouchs.iSVCost/0.83 as 单价,
v_bom_opcomponent_rpt.BaseQtyN as 基本用量,
PurSettleVouchs.iSVCost/0.83*v_bom_opcomponent_rpt.BaseQtyN as 小计 into #
from Inventory RIGHT JOIN (v_bom_parent_rpt LEFT JOIN (v_bom_opcomponent_rpt LEFT JOIN (PurSettleVouch RIGHT JOIN (PurSettleVouchs LEFT JOIN Inventory v1 on PurSettleVouchs.cInvCode=v1.cInvCode) on PurSettleVouch.PSVID=PurSettleVouchs.PSVID) on v_bom_opcomponent_rpt.InvCode=PurSettleVouchs.cInvCode) on v_bom_parent_rpt.BomId=v_bom_opcomponent_rpt.BomId) on Inventory.cInvCode=v_bom_parent_rpt.InvCode
order by v_bom_parent_rpt.InvCode
select max(结算时间),子件编码 from # a where #.子件编码=a.子件编码