use Dynamics
go
--海关单位转库存单位
------------------------------------------
--创建表及其相关字段
drop table Jimmy_UnitConversionTest
go
CREATE TABLE Jimmy_UnitConversionTest
(
ItemId varchar(41) default('') not null,
InventQty decimal(24,4) default(0) not null,
InventUnitid varchar(10) default('') not null,
CSMQty decimal(24,4) default(0) not null,
CSMUnitId varchar(10) default('') not null,
factor decimal(24,12) default(0) not null
)
insert into Jimmy_UnitConversionTest(ItemId,InventQty)
values('Col-1100',10)
update a set InventUnitid = b.unitid
from Jimmy_UnitConversionTest a join InventTableModule b on a.itemid = b.itemid
where b.moduletype = 0
GO
select * from Jimmy_UnitConversionTest where InventUnitid = ''
update a set a.factor = 1
from Jimmy_UnitConversionTest a
where a.CSMUnitId = a.InventUnitid and factor = 0
GO
select * from Jimmy_UnitConversionTest where factor = 0
GO
-- 更新与库存单位的换算
update a set a.factor = b.factor
from Jimmy_UnitConversionTest a
join unitconvert b on a.itemid = b.itemid and a.CsmUnitId = b.fromunit and a.Inventunitid = b.tounit and a.factor = 0
GO
-- 反向更新
update a set a.factor = 1 / b.factor
from Jimmy_UnitConversionTest a
join unitconvert b on a.itemid = b.itemid and a.CSMUnitId = b.tounit and a.Inventunitid = b.fromunit and a.factor = 0
GO
-- 系统不存在的换算率:
select * from Jimmy_UnitConversionTest where factor = 0
go
-- 找不到的直接用1做换算率
update a set a.InventQty = case
when factor <> 0 then (1 / factor) * CSMQty
when factor = 0 then (1 / 1) * CSMQty end
from Jimmy_UnitConversionTest a
GO