Untitled

mail@pastecode.io avatarunknown
plain_text
23 days ago
3.4 kB
0
Indexable
Never
begin tran

  -- exec dbo.ToggleFeature 'PostingInSortPlaceCellClean', 1;

  -- exec dbo.ToggleFeatureForPhysicalStore 'PostingInSortPlaceCellClean', 12856, 1;
  

  select count(1)
  -- -- pspcl.Id,
  -- p.PhysicalStoreID,
  -- count(1) over (partition by p.PhysicalStoreID) QtyOnPS,
  -- p.PostingNumber, 
  -- convert(date, p.StateChangeMoment),
  -- count(1) over (partition by convert(date, p.StateChangeMoment)) QtyPerDay,
  -- count(1) over (partition by datepart(month, p.StateChangeMoment)) QtyPerMonth,
  -- p.PostingStateId, 
  -- -- pb.SortMethodID, 
  -- --count(1) over (partition by pb.SortMethodID),

  -- pspc.SortPlaceCellId,
  -- spc.SortPlaceCellStatusID,
  -- spc.SortPlaceId,
  -- -- -- sp.*
  -- *
from dbo.PostingInSortPlaceCell pspc with (nolock)

      --join dbo.PostingBatch pb with (nolock) on pb.Id = pspcl.PostingBatchId 
      join dbo.Posting p with (nolock) on p.Id = pspc.PostingId 
      --join dbo.PostingInSortPlaceCell pspcl with (nolock) on pspcl.PostingId = p.Id
      join dbo.SortPlaceCell spc on spc.Id = pspc.SortPlaceCellId
      --join dbo.SortPlace sp on spc.Id = spc.SortPlaceId
      join dbo.PhysicalStoreAttribute psa on psa.PhysicalStoreID = p.PhysicalStoreID and psa.PhysicalStoreAttributeTypeID = 23 and psa.Value in ('1', '2')
-- where p.PhysicalStoreID = 15713
-- order by p.PostingStateId,
--       p.PhysicalStoreID,
--       p.StateChangeMoment desc
      -- and p.PhysicalStoreID = 15713
      -- --and pspcl.Id = 323746402
      --and pspcl.OutMoment is null
      --and p.PostingStateId < 50

  Update sc 
  set sc.Value = '1'
  from dbo.SysConfig sc
  where sc.Name = 'PostingInSortPlaceCellCleanLoging';

  Update sc 
  set sc.Value = '365'
  from dbo.SysConfig sc
  where sc.Name = 'PostingInSortPlaceCellDaysToHold';


  exec dbo.PostingInSortPlaceCellCleanForJob;


  select * from dbo.PostingInSortPlaceCellLog
  where id > 324825383;

  select count(1)
  -- -- pspcl.Id,
  -- p.PhysicalStoreID,
  -- count(1) over (partition by p.PhysicalStoreID) QtyOnPS,
  -- p.PostingNumber, 
  -- convert(date, p.StateChangeMoment),
  -- count(1) over (partition by convert(date, p.StateChangeMoment)) QtyPerDay,
  -- count(1) over (partition by datepart(month, p.StateChangeMoment)) QtyPerMonth,
  -- p.PostingStateId, 
  -- -- pb.SortMethodID, 
  -- --count(1) over (partition by pb.SortMethodID),

  -- pspc.SortPlaceCellId,
  -- spc.SortPlaceCellStatusID,
  -- spc.SortPlaceId,
  -- -- -- sp.*
  -- *
from dbo.PostingInSortPlaceCell pspc with (nolock)

      --join dbo.PostingBatch pb with (nolock) on pb.Id = pspcl.PostingBatchId 
      join dbo.Posting p with (nolock) on p.Id = pspc.PostingId 
      --join dbo.PostingInSortPlaceCell pspcl with (nolock) on pspcl.PostingId = p.Id
      join dbo.SortPlaceCell spc on spc.Id = pspc.SortPlaceCellId
      --join dbo.SortPlace sp on spc.Id = spc.SortPlaceId
      join dbo.PhysicalStoreAttribute psa on psa.PhysicalStoreID = p.PhysicalStoreID and psa.PhysicalStoreAttributeTypeID = 23 and psa.Value in ('1', '2')
-- where p.PhysicalStoreID = 15713
-- order by p.PostingStateId,
--       p.PhysicalStoreID,
--       p.StateChangeMoment desc
      -- and p.PhysicalStoreID = 15713
      -- --and pspcl.Id = 323746402
      --and pspcl.OutMoment is null
      --and p.PostingStateId < 50

rollback 


cкрипт для тестирования