Calculate Inventory - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Calculate Inventory

Author

Topic

jwells
Starting Member

17 Posts

Posted - 2013-08-26 : 18:01:46

I'm trying to create an inventory report for service trucks by adding all the transactions that were used to restock the truck and subtract the transactions where the parts were used on an invoice and removed from the truck. All the transactions are in the same table. The fields that would be relevant are PartID, QTY, WhsTo and WhsFrom. If I wanted to calculated stock levels for truck 16 I would select all transactions that have a value of 16 in either WhsTo or WhsFrom. If WhsTo contained 16 then I would want to add QTY. If WhsFrom value was 16 then I would want to subtract QTY. I would want it grouped by distinct PartID. I don't know how to structure the Select statement to decide whether to add or subtract. Can someone help me out?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-08-26 : 18:36:00

My suggestion would be:

Querying for a single truck:


select partid
      ,sum(qty*case when whsfrom=16 the -1 else 1 end) as qty
  from yourtable
 where whsto=16
    or whsfrom=16
 group by partid

Querying for all trucks:


select partid
      ,whs
      ,sum(qty) as qty
  from (select partid
              ,whsfrom as whs
              ,sum(qty)*-1 as qty
          from yourtable
         group by partid
                 ,whsfrom
        union all
        select partid
              ,whsto as whs
              ,sum(qty) as qty
          from yourtable
         group by partid
                 ,whsto
       ) as t
 group by partid
         ,whs

[/code]

jwells
Starting Member

17 Posts

Posted - 2013-08-26 : 18:36:36

I think I have it worked out. Is this right?

SELECT DISTINCT dbo.Parts.PartID, dbo.Parts.Description, SUM(CASE WHEN fromloc = 16 THEN quantity WHEN toloc = 16 THEN 0 - quantity END) AS QTY
FROM dbo.WOParts INNER JOIN
dbo.Parts ON dbo.WOParts.PartID = dbo.Parts.ID
WHERE (dbo.WOParts.FromLoc = 16) OR
(dbo.WOParts.ToLoc = 16)
GROUP BY dbo.Parts.PartID, dbo.Parts.Description

jwells
Starting Member

17 Posts

Posted - 2013-08-26 : 18:43:42

Thanks for your help. This wasn't as difficult as thought it would be.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-27 : 01:45:12

quote:
Originally posted by jwells

I think I have it worked out. Is this right?

SELECT ~~DISTINCT~~ dbo.Parts.PartID, dbo.Parts.Description, SUM(CASE WHEN fromloc = 16 THEN quantity WHEN toloc = 16 THEN 0 - quantity END) AS QTY
FROM dbo.WOParts INNER JOIN
dbo.Parts ON dbo.WOParts.PartID = dbo.Parts.ID
WHERE (dbo.WOParts.FromLoc = 16) OR
(dbo.WOParts.ToLoc = 16)
GROUP BY dbo.Parts.PartID, dbo.Parts.Description

you dot need DISTINCT here as you're already applying GROUP BY on relevant columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jwells
Starting Member

17 Posts

Posted - 2013-08-29 : 13:06:10

Thanks for the help. I'm having issues expanding this statement to include my stock levels. I have another table that holds the minimum and maximum stock levels for each part for each truck. So as in the original statement I'm summing the quantities used on a particular truck to find the available stock and trying to associate the MinStock and MaxStock for that part on that truck. What it should return is at least the number of records in the StockLevels (min, max and summed qty) as well as any other parts that were used in WOParts that are not part of Stocklevels. I know there are 370 records for truck 16 in StockLevels but I'm only returning 356 records, so I know I'm missing something. Here is the statement:

SELECT TOP 100 PERCENT dbo.Parts.PartID, dbo.Parts.Description, dbo.StockLevels.MinStock, dbo.StockLevels.MaxStock,
SUM(CASE WHEN fromloc = 16 THEN quantity * - 1 WHEN toloc = 16 THEN quantity END) AS QTY
FROM dbo.WOParts LEFT OUTER JOIN
dbo.StockLevels ON dbo.WOParts.PartID = dbo.StockLevels.PartID LEFT OUTER JOIN
dbo.Parts ON dbo.Parts.ID = dbo.WOParts.PartID
WHERE (dbo.WOParts.FromLoc = 16) OR
(dbo.WOParts.ToLoc = 16) OR
(dbo.StockLevels.WhsLocID = 16)
GROUP BY dbo.Parts.PartID, dbo.Parts.Description, dbo.StockLevels.MinStock, dbo.StockLevels.MaxStock
ORDER BY dbo.Parts.PartID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-30 : 00:23:41

try this and see if it gives desired result


SELECT TOP 100 PERCENT dbo.Parts.PartID, dbo.Parts.Description, dbo.StockLevels.MinStock, dbo.StockLevels.MaxStock, 
SUM(CASE WHEN fromloc = 16 THEN quantity * - 1 WHEN toloc = 16 THEN quantity END) AS QTY
FROM dbo.WOParts LEFT OUTER JOIN
dbo.StockLevels ON dbo.WOParts.PartID = dbo.StockLevels.PartID 
AND dbo.StockLevels.WhsLocID = 16
LEFT OUTER JOIN
dbo.Parts ON dbo.Parts.ID = dbo.WOParts.PartID
WHERE (dbo.WOParts.FromLoc = 16) OR
(dbo.WOParts.ToLoc = 16)
GROUP BY dbo.Parts.PartID, dbo.Parts.Description, dbo.StockLevels.MinStock, dbo.StockLevels.MaxStock
ORDER BY dbo.Parts.PartID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources