August 6, 2012 at 11:11 am
what have you tried so far?
this kind of looks like homework, so I'd prefer to help you learn how, rather than throw an answer out there.
you have more than one table, right? one for inventory and another for orders?
you want a query or a view or a static table for the results?
Lowell
August 6, 2012 at 11:12 am
krishna-1031038 (8/6/2012)
Hello,I have Inventory table
productID | warehouseID | qtyAvail
A | Loc1 | 100
A | Loc2 | 20
A | Loc3 | 250
A | Loc4 | 1000
User enters quantity = 110, I would like a query or a stored procedure that would return the following -
productID | warehouseID | qtyAvail | Allocated
A | Loc1 | 100 | 100
A | Loc2 | 20 | 10
A | Loc3 | 250 | 0
A | Loc4 | 1000 | 0
If the user enter 500 then the result would be
productID | warehouseID | qtyAvail | Allocated
A | Loc1 | 100 | 100
A | Loc2 | 20 | 20
A | Loc3 | 250 | 250
A | Loc4 | 1000 | 130
Any help would be appreciated.
Thanks,
Kris
are you always going to use the warehouseId as precedent for selection?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 6, 2012 at 11:12 am
yes
August 6, 2012 at 11:15 am
krishna-1031038 (8/6/2012)
yes
to which question(s) are you you replying "yes"?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 6, 2012 at 11:16 am
Hello Lowell,
to answer your question, I have tried using a cursor to loop and do this, I was hoping to find a better solution.
yes I do have a orders table, the query is just to get the allocation preview first
hope this helps
August 6, 2012 at 11:17 am
sorry,
I meant to reply to this one, yes I would use warehouseId as precedent for selection
August 6, 2012 at 11:36 am
Lowell (8/6/2012)
what have you tried so far?this kind of looks like homework, so I'd prefer to help you learn how, rather than throw an answer out there.
you have more than one table, right? one for inventory and another for orders?
you want a query or a view or a static table for the results?
Hello Lowell,
Here is what the concept of what I currently have
DECLARE
@crProductID VARCHAR(25)
,@crWarehouseID VARCHAR(50)
,@crPk INT
,@crQtyAvail DECIMAL(18,4)
,@dblQtyEntered DECIMAL(18,4)
,@dblQtyToAll DECIMAL(18,4)
SET @dblQtyEntered = 110
DECLARE @tempInventory TABLE (
pk INT IDENTITY(1,1)
,strProductID VARCHAR(25)
,strWarehouseID VARCHAR(50)
,dblQtyAvail DECIMAL(18,4)
,dblQtyAll DECIMAL(18,4)
)
INSERT INTO @tempInventory(strProductID,strWarehouseID,dblQtyAvail,dblQtyAll)
SELECT 'A','Loc1',100,0
UNION
SELECT 'A','Loc2',20,0
UNION
SELECT 'A','Loc3',250,0
UNION
SELECT 'A','Loc4',1000,0
--SELECT * FROM @tempInventory
--loop
DECLARE AutoAllocation CURSOR FOR
SELECT
pk
,strProductID
,strWarehouseID
,dblQtyAvail
FROM
@tempInventory
ORDER BY
strWarehouseID ASC
OPEN AutoAllocation
FETCH NEXT FROM AutoAllocation
INTO @crPk,@crProductID,@crWarehouseID,@crQtyAvail
WHILE @@FETCH_STATUS = 0
BEGIN
IF @dblQtyEntered > 0
BEGIN
SET @dblQtyToAll = CASE WHEN @crQtyAvail > @dblQtyEntered THEN @dblQtyEntered ELSE @dblQtyEntered - ABS(@crQtyAvail - @dblQtyEntered) END
UPDATE @tempInventory SET
dblQtyAll = @dblQtyToAll
WHERE
pk = @crPk
SET @dblQtyEntered = @dblQtyEntered - @dblQtyToAll
END
FETCH NEXT FROM AutoAllocation
INTO @crPk,@crProductID,@crWarehouseID,@crQtyAvail
END
CLOSE AutoAllocation
DEALLOCATE AutoAllocation
SELECT
strProductID AS productID
,strWarehouseID AS warehouseID
,dblQtyAvail AS qtyAvail
,dblQtyAll AS qtyAvail
FROM @tempInventory
August 6, 2012 at 11:46 am
I came up with the following. It will probably need work to fit into your actual solution but it gives you an idea.
select
productID,
warehouseID,
qtyAvail
into
#Inventory
from
(values ('A','Loc1',100),('A','Loc2',20),('A','Loc3',250),('A','Loc4',1000))dt(productID,warehouseID,qtyAvail)
;
go
select * from #Inventory;
go
declare @Item char(1) = 'A',
@Qty int = 110;
with
BaseData as (
select
row_number() over (order by warehouseID) rn,
productID,
warehouseID,
qtyAvail
from
#Inventory
where
productID = @Item
),
Alloc as (
select
rn,
productID,
warehouseID,
qtyAvail,
case when @Qty < qtyAvail then @Qty else qtyAvail end as Allocated,
@Qty - case when @Qty < qtyAvail then @Qty else qtyAvail end ToAllocate
from
BaseData bd
where
rn = 1
union all
select
bd.rn,
bd.productID,
bd.warehouseID,
bd.qtyAvail,
case when a.ToAllocate < bd.qtyAvail then ToAllocate else bd.qtyAvail end as Allocated,
a.ToAllocate - case when a.ToAllocate < bd.qtyAvail then a.ToAllocate else bd.qtyAvail end ToAllocate
from
BaseData bd
inner join Alloc a
on (bd.rn = a.rn + 1)
)
select
productID, warehouseID, qtyAvail, Allocated
from
Alloc;
go
declare @Item char(1) = 'A',
@Qty int = 500;
with
BaseData as (
select
row_number() over (order by warehouseID) rn,
productID,
warehouseID,
qtyAvail
from
#Inventory
where
productID = @Item
),
Alloc as (
select
rn,
productID,
warehouseID,
qtyAvail,
case when @Qty < qtyAvail then @Qty else qtyAvail end as Allocated,
@Qty - case when @Qty < qtyAvail then @Qty else qtyAvail end ToAllocate
from
BaseData bd
where
rn = 1
union all
select
bd.rn,
bd.productID,
bd.warehouseID,
bd.qtyAvail,
case when a.ToAllocate < bd.qtyAvail then ToAllocate else bd.qtyAvail end as Allocated,
a.ToAllocate - case when a.ToAllocate < bd.qtyAvail then a.ToAllocate else bd.qtyAvail end ToAllocate
from
BaseData bd
inner join Alloc a
on (bd.rn = a.rn + 1)
)
select
productID, warehouseID, qtyAvail, Allocated
from
Alloc;
go
drop table #Inventory;
go
Viewing 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply