CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Morad
Morad

Reputation: 3

Add Math function to SQL Query

New to SQL and I am trying to run a query that pulls all our item codes, lot number, and qty on hand.

Each lot number has multiple entries due to adjustments. I need a way of running my query and having it add or subtract to get the actual qty on hand for each lot and only show me lots that are in the negatives. I have tried playing with SSRS but I cant get it right. I'm using SQL 2008R2.

SELECT
  IMLAYER.ITEM_CODE
 ,IMMSTR.ITEM_DESC
 ,IMLAYER.LOT_NO
 ,IMLAYER.QTY_ON_HAND
FROM
  IMLAYER
INNER JOIN
  IMMSTR
    ON
    IMLAYER.ITEM_CODE = IMMSTR.ITEM_CODE
WHERE
  (IMLAYER.QTY_ON_HAND < 0);

Upvotes: 0

Views: 221

Answers (1)

Jacob H
Jacob H

Reputation: 2505

I believe I understand the requirements correctly, but if not please comment and I can update the query:

SELECT
    M.ITEM_CODE
   ,M.ITEM_DESC
   ,L.LOT_NO
   ,'SUM_OF_QTY_ON_HAND' = SUM(L.QTY_ON_HAND)
FROM
    IMLAYER L
INNER JOIN
    IMMSTR M
    ON L.ITEM_CODE = M.ITEM_CODE
GROUP BY
    M.ITEM_CODE
    ,M.ITEM_DESC
    ,L.LOT_NO
HAVING
    SUM(L.QTY_ON_HAND) < 0

HAVING is the trick you are looking for to be able to use an aggregate function for filtering.

Upvotes: 1

Related Questions