We help IT Professionals succeed at work.
Get Started

Use of ABS (Absolute Value) in SQL

W D
W D asked
on
5,112 Views
Last Modified: 2010-05-18
Hello,
I have the following code I'm going to make into a stored procedure:
-- Create Temp Table for holding Warehouse Inventory values
CREATE TABLE #tmpWIQtyResults (
      [RowNumber][INT] IDENTITY (1,1) PRIMARY KEY NOT NULL,
      [ItemID][CHAR] (30),
      [Qty][INT]
      )

-- Totalling the inventory across all warehouse locations
INSERT #tmpWIQtyResults ([ItemID],[Qty])
SELECT DISTINCT w.WI_Item, SUM(ISNULL(w.WI_Qty,0))
FROM WIN..WiMstr w
      INNER JOIN WIN..ItemMstr i ON
      w.WI_Item = i.IM_Item AND
      w.WI_CustNo = i.IM_CustNo  
WHERE (w.WI_CustNo = 45)
AND (i.ProductType = 'Product')
AND i.ProductType NOT IN ('ASSIGN ONE', 'Product Family Parent', 'CD Image')
AND (w.WI_LOC <> 'CYCERR')
GROUP BY w.WI_Item


-- Create Temp Table for holding EQOH values
CREATE TABLE #tmpEQOHResults (
      [RowNumber][INT] IDENTITY (1,1) PRIMARY KEY NOT NULL,
      [ItemID][CHAR] (30),
      [EQOH][INT]
      )

-- Obtaining the EQOH value for the given @eDate
INSERT #tmpEQOHResults ([ItemID], [EQOH])
SELECT DISTINCT a.ItemID,
ISNULL([End QOH],0) + ISNULL([Ttl Daily Activity],0) + ISNULL([Ttl Cycle Counted], 0) - ISNULL([Ttl Returned to Inventory],0) - ISNULL([Ttl Received],0) + ISNULL([Ttl Damaged],0) + ISNULL([Ttl Recycled],0)
AS 'EQOH'
FROM (
SELECT DISTINCT i.IM_Item AS 'ItemID',
SUM(CASE WHEN r.RF_XCode IN ('CC_ITEM','CC_LOC')  --Cycle Counts
      THEN r.RF_QTY  
      ELSE 0
      END) AS 'Ttl Cycle Counted',
SUM(CASE WHEN r.RF_XCode IN ('RETURNS','LO_RTRN')  --Returns
      THEN r.RF_Qty
      ELSE 0
      END) AS 'Ttl Returned to Inventory',
SUM(CASE WHEN r.RF_XCode IN ('IM_RCPT','PO_RCPT','MOVE_PUT')  --Receipts
      THEN r.RF_Qty
      ELSE 0
      END) AS 'Ttl Received',
SUM(CASE WHEN r.RF_XCode IN('PICK','LO_PICK','KIT_BLD','MOVE_PIC')  --Picks
      THEN r.RF_Qty
      ELSE 0
      END) AS 'Ttl Daily Activity',
ABS(SUM(CASE WHEN r.RF_XCode IN('DMGD')  --Damaged
      THEN r.RF_Qty
      ELSE 0
      END)) AS 'Ttl Damaged',
SUM(CASE WHEN r.RF_XCode IN('RECYCLE')  --Recycled
      THEN r.RF_Qty
      ELSE 0
      END) AS 'Ttl Recycled',
wtmp.Qty AS 'End QOH'
FROM WIN..RFAudit r
      INNER JOIN WIN..ItemMstr i ON
      r.RF_Item = i.IM_Item AND
      r.RF_CustNo = i.IM_CustNO  
      LEFT OUTER JOIN #tmpWIQtyResults wtmp ON
      r.RF_Item = wtmp.ItemID
WHERE (r.RF_CustNo = '45')
AND (r.RF_Date >= '12/9/2006')  --this is the @eDate for testing
AND (i.ProductType = 'Product')
AND i.ProductType NOT IN ('ASSIGN ONE', 'Product Family Parent', 'CD Image')
AND r.RF_XCode IN ('CC_ITEM','CC_LOC','RETURNS','LO_RTRN','IM_RCPT','PO_RCPT','PICK','LO_PICK','KIT_BLD','MOVE_PIC','MOVE_PUT','DMGD','RECYCLE')
GROUP BY i.IM_Item, wtmp.Qty
) a

... and then I have additional code to get the beginning QOH for any given start date (@sDate), but that isn't relevant for the inquiry...

The problem is, all of the products' end quantity on hand and their activities (picks, receipts, etc) come out OK except for those products who have been cycle counted. Cycle counts can be either negative (if you're cycle counting down something) or positive (if you're cycle counting up something).
I've tried putting ABS like this:
...
ABS(SUM(CASE WHEN r.RF_XCode IN ('CC_ITEM','CC_LOC')  --Cycle Counts
      THEN r.RF_QTY  
      ELSE 0
      END))AS 'Ttl Cycle Counted',

and left this statement above it in the SELECT as an addition operation: + ISNULL([Ttl Cycle Counted], 0)

Here's a sample product's results:
ItemID      EQOH for 12/9/2006
5900      -2306
5900      2494

The current (12/14/06) QOH for item 5900 is 94 (the results from the temp table #tmpWIQtyResults). This is correct.
The item had 2 cycle counts from 12/9/06 to current:
-1296.000  on 12/10
-1104.000 on 12/11
Total cycle counts =  -2400

So, when we turn back the clock to get the EQOH for 12/9/2006, we should get 2494.

If I take out the ABS in front of the CASE statement, the result is:
ItemID      EQOH for 12/9/2006
5900      -2306

If I change    + ISNULL([Ttl Cycle Counted], 0)   to   - ISNULL([Ttl Cycle Counted], 0) in the SELECT statement and still leave out the ABS in front of the CASE statement I get this result:
ItemID      EQOH for 12/9/2006
5900      -2306
5900      2494
5900      2494

Does anyone have any thoughts about this? I hope I made my inquiry clear enough, let me know if I haven't. Am I using ABS incorrectly in my SQL? If I am, where should I put it for summing up the cycle counts?

Best regards,
wdelaney







Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE