TSQL Warehouse Qty Running Balance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

TSQL Warehouse Qty Running Balance

Author

Topic

mkhalid
Starting Member

9 Posts

Posted - 2011-02-26 : 17:28:40

Hello All,

I have the following data

WarehouseID,ItemID,Qty
1,2001,15
2,2001,10
2,2001,5
3,2001,10
3,2001,30

I need some sort of query or cursor which can deduct the qty from the warehouse according to their limit.

Like if i request qty 16 for item 2001

From Warehouse 1 15 qty will be deducted
From Warehouse 2 1 qty will be deducted
From Warehouse 3 0 qty will be deducted
and so on.

i tried everything from cursors to temptables, but couldnt find any solution.

any suggestions?

thanks

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-02-26 : 18:24:06

Please try to explain this with more detail. I cannot understand your problem from this post.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-02-26 : 18:44:53

Here is a way to do it:


declare @itemid  int = 2001;
declare @RequestQty  int = 16;

if not exists( select * from YourTable where @itemId = itemId having sum(qty) >= @RequestQty)
   print 'Don't have enough to give you. Go away'
else
begin

with a as
(
   select top 1
      WarehouseId,
      ItemId,
      qty as AvailableQty,
      DispensedQty = case when @RequestQty <= qty then @RequestQty else qty end,
      RemainingQty = case when @RequestQty <= qty then qty-@RequestQty else 0 end,
      RemainingRequest = @RequestQty-qty
   from
      YourTable
   where
      qty > 0 and @itemId = ItemId
   order by
      warehouseId
   union all
   select
      t.WarehouseId,
      t.ItemId,
      t.qty as AvailableQty,
      DispensedQty = case when a.RemainingRequest <= t.qty then a.RemainingRequest else t.qty end,
      RemainingQty = case when a.RemainingRequest <= t.qty then t.qty-a.RemainingRequest else 0 end,
      RemainingRequest = case when a.RemainingRequest > t.qty then a.RemainingRequest-t.qty else 0 end
   from
      YourTable t
      inner join A on a.WarehouseId+1 = t.WarehouseId
   where
      a.RemainingRequest > 0 and @itemId = t.ItemId
)
select * from A;
end

If that looks like it is doing what you need it to do, and if you want to update the table as requests come in, you can replace the "select * from A" with an update statement that joins A to YourTable to update the quantities in YourTable.

If you will need to use more than 100 WarehouseId's in a single request, look up MAXRECURSION query hint to increase the recursion count.

mkhalid
Starting Member

9 Posts

Posted - 2011-02-27 : 01:50:21

thanks for the quick reply,

will the query work if i have a different sort order

maybe the warehouseid is 2,1,3

the warehouse table can contain the same warehouse with different quantities.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-02-28 : 07:15:54

The data in a table is an unordered collection even though it may appear to be ordered in some manner when you retrieve the data from the table. So you have to first figure out what characteristic of the data or which column(s) define the sort order.

For example, you might say that you want to dispense from the record that has the largest quantity first and then follow through in the descending order. If that is what you want to do, you can first order the data using that criterion, and then dispense. The code then could be something like this:

with CTE as
(
   select
      row_number() over (order by Qty desc) as OrderingId,
      *
   from
      YourTable
   where
      ItemId = @ItemId
),
a as
(
   select
      OrderingId,
      WarehouseId,
      ItemId,
      qty as AvailableQty,
      DispensedQty = case when @RequestQty <= qty then @RequestQty else qty end,
      RemainingQty = case when @RequestQty <= qty then qty-@RequestQty else 0 end,
      RemainingRequest = @RequestQty-qty
   from
      CTE
   where
      qty > 0 and OrderingId = 1
   union all
   select
      OrderingId,
      t.WarehouseId,
      t.ItemId,
      t.qty as AvailableQty,
      DispensedQty = case when a.RemainingRequest <= t.qty then a.RemainingRequest else t.qty end,
      RemainingQty = case when a.RemainingRequest <= t.qty then t.qty-a.RemainingRequest else 0 end,
      RemainingRequest = case when a.RemainingRequest > t.qty then a.RemainingRequest-t.qty else 0 end
   from
      CTE t
      inner join A on a.OrderingId+1 = t.OrderingId
   where
      a.RemainingRequest > 0 
)
select * from A;

mkhalid
Starting Member

9 Posts

Posted - 2011-03-01 : 11:24:58

you are a genius.

thanks, it works great.

Sunita, is there a possibility of overselling the qty from the last warehouse.

say if total qty is 50 and requested is 100 and the last warehouse the qty dispensed would be in negative.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-02 : 10:33:43

Glad it worked out for you. But, Genius? No! Unemployed, with lots of time on my hands? Yes!!

It would not dispense more than what is available, but you may want to check whether there is enough to dispense before you actually dispense it as I had indicated in my first response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources