sql to allocate quanitity according to available quantity

Question

Post reply

sql to allocate quanitity according to available quantity

krishna-1031038

SSC-Addicted

Points: 427
More actions
August 6, 2012 at 11:08 am

#259709

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

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323740 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

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

krishna-1031038 SSC-Addicted Points: 427 More actions · Answer 3

krishna-1031038

SSC-Addicted

Points: 427

August 6, 2012 at 11:12 am

#1522182

yes

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

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

krishna-1031038 SSC-Addicted Points: 427 More actions · Answer 5

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

krishna-1031038 SSC-Addicted Points: 427 More actions · Answer 6

sorry,

I meant to reply to this one, yes I would use warehouseId as precedent for selection

krishna-1031038 SSC-Addicted Points: 427 More actions · Answer 7

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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