June 27, 2016 at 2:58 am
i have this table with this data
ID Store SKU AvailableQty ReceivingStore QtyDemand Transferred
1 ST1 100 20 ST2 14 14
2 ST1 100 20 ST3 25 6
3 ST1 100 20 ST4 25 0
how to distribute available quantity based on demand quantity and calculated same in column transferred.
June 27, 2016 at 3:21 am
quick question, you need TransferedQty column result through the query ???
June 27, 2016 at 3:25 am
twin.devil (6/27/2016)
quick question, you need TransferedQty column result through the query ???
yes exactly
June 27, 2016 at 4:07 am
declare @Transaction table
(
ID int
, Store char(3)
, SKU int
, AvailableQty int
, ReceivingStore char(3)
, QtyDemand int
)
insert into @Transaction
select 1, 'ST1', 100, 20, 'ST2', 14union all
select 2, 'ST1', 100, 20, 'ST3', 25union all
select 3, 'ST1', 100, 20, 'ST4', 25
select ID, Store, SKU, AvailableQty, ReceivingStore, QtyDemand , case when bal < 1 then
case when QtyDemand + bal < 0 then 0 else QtyDemand + bal end
else AvailableQty - bal end as transfered
from
(
select *
, (AvailableQty - SUM(t.QtyDemand ) OVER (Partition by sku ORDER BY ID ROWS UNBOUNDED PRECEDING)) as bal
from @Transaction t
) A
hope it helps.
June 27, 2016 at 5:10 am
twin.devil (6/27/2016)
declare @Transaction table
(
ID int
, Store char(3)
, SKU int
, AvailableQty int
, ReceivingStore char(3)
, QtyDemand int
)
insert into @Transaction
select 1, 'ST1', 100, 20, 'ST2', 14union all
select 2, 'ST1', 100, 20, 'ST3', 25union all
select 3, 'ST1', 100, 20, 'ST4', 25
select ID, Store, SKU, AvailableQty, ReceivingStore, QtyDemand , case when bal < 1 then
case when QtyDemand + bal < 0 then 0 else QtyDemand + bal end
else AvailableQty - bal end as transfered
from
(
select *
, (AvailableQty - SUM(t.QtyDemand ) OVER (Partition by sku ORDER BY ID ROWS UNBOUNDED PRECEDING)) as bal
from @Transaction t
) A
hope it helps.
thank you for your help , it worked successfully but in some cases the transferred Qty more than QtyDemand
June 27, 2016 at 5:14 am
Share some of the sample data for which you are having issues.
June 27, 2016 at 5:21 am
twin.devil (6/27/2016)
Share some of the sample data for which you are having issues.
you can change in the value of QtyDemand and it will result the issue
declare @Transaction table
(
ID int
, Store char(3)
, SKU int
, AvailableQty int
, ReceivingStore char(3)
, QtyDemand int
)
insert into @Transaction
select 1, 'ST1', 100, 20, 'ST2', 2union all
select 2, 'ST1', 100, 20, 'ST3', 5union all
select 3, 'ST1', 100, 20, 'ST4', 25
select ID, Store, SKU, AvailableQty, ReceivingStore, QtyDemand , case when bal < 1 then
case when QtyDemand + bal < 0 then 0 else QtyDemand+bal end
else AvailableQty - bal end as transfered
from
(
select *
, (AvailableQty - SUM(t.QtyDemand ) OVER (Partition by sku ORDER BY ID ROWS UNBOUNDED PRECEDING)) as bal
from @Transaction t
) A
June 27, 2016 at 5:38 am
also if total demand less than or equal available it will result the issue,
my issue if total demand more than available but if less or equal i already add QtyDemand
case when availableQty >=TotalDemand then QtyDemand else ...here the issue how to stop if it exceeded the available Qty
June 27, 2016 at 6:39 am
declare @Transaction table
(
ID int
, Store char(3)
, SKU int
, AvailableQty int
, ReceivingStore char(3)
, QtyDemand int
)
insert into @Transaction
--- Original Case
select 1, 'ST1', 100, 20, 'ST2', 14union all
select 2, 'ST1', 100, 20, 'ST3', 25union all
select 3, 'ST1', 100, 20, 'ST4', 25
;
--- Sample Case 1
insert into @Transaction
select 4, 'ST1', 101, 20, 'ST2', 2union all
select 5, 'ST1', 101, 20, 'ST3', 5union all
select 6, 'ST1', 101, 20, 'ST4', 25
;
--- Sample Case 2
insert into @Transaction
select 4, 'ST1', 102, 20, 'ST2', 20union all
select 5, 'ST1', 102, 20, 'ST3', 5union all
select 6, 'ST1', 102, 20, 'ST4', 25
;
WITH CTE
AS
(
select a.*, case when a.rn = 1 then AvailableQty - QtyDemand else null end as OpenBal
from
(
select *
, ROW_NUMBER() OVER (Partition by SKU ORDER BY ID) as rn
from @Transaction
) A
)
Select ID,Store, Sku, AvailableQty, ReceivingStore, QtyDemand, case when transfered = 0 then PrevBalc else transfered end as transfered
from
(
select *, case when rn = 1 then QtyDemand else case when closeing < 0 then 0 else QtyDemand end end as transfered
, case when LAG(closeing) OVER (Partition by sku ORDER BY ID) > 0 THEN LAG(closeing) OVER (Partition by sku ORDER BY ID) else 0 end AS PrevBalc
from
(
select * , SUM(coalesce(OpenBal, -1 * QtyDemand)) OVER (Partition by sku ORDER BY ID ROWS UNBOUNDED PRECEDING) as closeing
from cte c
) A
) A
Edited: Added two more cases for understanding.
Hope it helps.
June 27, 2016 at 6:53 am
excellent , thanks , appreciating your help .
June 27, 2016 at 6:56 am
you are welcome 🙂
June 27, 2016 at 7:36 am
Can you explain why you are adding QtyDemand here?
My doubt is since for 1st row the demand is supplied using the quantity in that row, then why should we SUM it?
June 27, 2016 at 7:46 am
Please Ignore my previous clarification as its based on your first solution.
June 28, 2016 at 1:55 am
After going through the query, if found i have missed a case and i have fixed it.
Following is the issue:
fixed issue: If 1st DemandQty is greater then the available quantity the Transferred column was showing DemandQty not the AvailableQty.
Here is the latest code, i have also added the CASE 3.
declare @Transaction table
(
ID int
, Store char(3)
, SKU int
, AvailableQty int
, ReceivingStore char(3)
, QtyDemand int
)
insert into @Transaction
--- Original Case
select 1, 'ST1', 100, 20, 'ST2', 14union all
select 2, 'ST1', 100, 20, 'ST3', 25union all
select 3, 'ST1', 100, 20, 'ST4', 25
;
--- Sample Case 1
insert into @Transaction
select 4, 'ST1', 101, 20, 'ST2', 2union all
select 5, 'ST1', 101, 20, 'ST3', 5union all
select 6, 'ST1', 101, 20, 'ST4', 25
;
--- Sample Case 2
insert into @Transaction
select 4, 'ST1', 102, 20, 'ST2', 20union all
select 5, 'ST1', 102, 20, 'ST3', 5union all
select 6, 'ST1', 102, 20, 'ST4', 25
;
--- Sample Case 3----- Added the case if 1st value is greater then the available quantity.
insert into @Transaction
select 4, 'ST1', 103, 20, 'ST2', 25union all
select 5, 'ST1', 103, 20, 'ST3', 5union all
select 6, 'ST1', 103, 20, 'ST4', 5
;
WITH CTE
AS
(
select a.*, case when a.rn = 1 then AvailableQty - QtyDemand else null end as OpenBal
from
(
select *
, ROW_NUMBER() OVER (Partition by SKU ORDER BY ID) as rn
from @Transaction
) A
)
Select ID,Store, Sku, AvailableQty, ReceivingStore, QtyDemand, case when transfered = 0 then PrevBalc else transfered end as transfered
from
(
select *
---- Updated fixed issue: 1st DemandQty is greater then the available quantity.
---, case when rn = 1 then QtyDemand else case when closeing < 0 then 0 else QtyDemand end end as transfered
, case
when rn = 1 then
Case
when QtyDemand > AvailableQty then AvailableQty
ELSE QtyDemand
END
else
case
when closeing < 0 then 0
else
QtyDemand
end
end as transfered
---- Ends here
, case when LAG(closeing) OVER (Partition by sku ORDER BY ID) > 0 THEN LAG(closeing) OVER (Partition by sku ORDER BY ID) else 0 end AS PrevBalc
from
(
select * , SUM(coalesce(OpenBal, -1 * QtyDemand)) OVER (Partition by sku ORDER BY ID ROWS UNBOUNDED PRECEDING) as closeing
from cte c
) A
) A
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply