update inventcount by stockCounting.sql

begin tran;
declare @temp table(ItemLocalInventoryAdjustmentId uniqueidentifier
, ItemLocalId uniqueidentifier
, ReferenceId uniqueidentifier
, ReferenceDetailId uniqueidentifier
, ReferenceTypeId uniqueidentifier
, AdjustmentQty int
, LocallyCreatedBy varchar(5)
, CreatedDateTime datetime
, CreatedBy varchar(10)
, ModifiedDateTime datetime
, ModifiedBy varchar(10)
, Version int
, PartitioningKey int);

with cte as (
select ilia.ItemLocalId
, max(ilia.CreatedDateTime) as date 
, SUM(ilia.AdjustmentQty) as totalAdjustment
from trio..ItemLocalInventoryAdjustment ilia 
where ReferenceId = '27733385-D4A0-4C55-A84E-A2C500009AB6'
group by ilia.ItemLocalId ),
cte1 as (
select ilia.ItemLocalId, ilia.AdjustmentQty, ccm.Code from trio..ItemLocalInventoryAdjustment ilia
inner join trio..CommonCodeMaster ccm on ilia.ReferenceTypeId = ccm.CommonCodeMasterId
inner join cte on ilia.ItemLocalId = cte.ItemLocalId
where ccm.Code in ('11', '10', '13')
and ilia.CreatedDateTime > cte.date ),
cte2 as (select cte.ItemLocalId, cte.totalAdjustment, il.InventoryQty, cast(-(cte.totalAdjustment / 3 * 2) as int) as adjust, il.InventoryQty + cast(-(cte.totalAdjustment / 3 * 2) as int) as newInventory from cte
inner join trio..ItemLocal il on cte.ItemLocalId = il.ItemLocalId
where cte.ItemLocalId not in (select ItemLocalId from cte1))
select * from cte2
--update il
--set il.InventoryQty = cte2.newInventory,
--il.Version = il.Version +1
--output NEWID(), inserted.ItemLocalId, '27733385-D4A0-4C55-A84E-A2C500009AB6', NEWID(), 'E4151D0B-9244-4272-A4A7-3BCAEF32FB14', cte2.adjust, null, GETDATE(), 'ple', GETDATE(), 'ple', 0, 1 
--into @temp(ItemLocalInventoryAdjustmentId, ItemLocalId, ReferenceId, ReferenceDetailId, ReferenceTypeId, AdjustmentQty, LocallyCreatedBy, CreatedDateTime, CreatedBy, ModifiedDateTime, ModifiedBy, Version, PartitioningKey)
--from trio..ItemLocal il 
--inner join cte2 on il.ItemLocalId = cte2.ItemLocalId

--insert into trio..ItemLocalInventoryAdjustment(ItemLocalInventoryAdjustmentId, ItemLocalId, ReferenceId, ReferenceDetailId, ReferenceTypeId, AdjustmentQty, LocallyCreatedBy, CreatedDateTime, CreatedBy, ModifiedDateTime, ModifiedBy, Version, PartitioningKey)
--select ItemLocalInventoryAdjustmentId, ItemLocalId, ReferenceId, ReferenceDetailId, ReferenceTypeId, AdjustmentQty, LocallyCreatedBy, CreatedDateTime, CreatedBy, ModifiedDateTime, ModifiedBy, Version, PartitioningKey from @temp

commit tran
rollback tran