CODEWITHSUNDEEP
mysqlsql
Teddybugs
Teddybugs

Reputation: 1244

SQL: get a negative value

i have a sql like this:

SELECT  PRODUCTID, UCASE(MYTYPE) AS MYTYPE, UNIT,
       SUM(CASE WHEN ADJUST_TYPE = 'ADD' THEN REORDER_QTY ELSE 0 END) AS IN_QTY,
       SUM(CASE WHEN ADJUST_TYPE = 'DEDUCT' THEN REORDER_QTY ELSE 0 END) AS OUT_QTY,
       SUM(CASE WHEN ADJUST_TYPE = 'ADD' THEN REORDER_QTY
                WHEN ADJUST_TYPE = 'DEDUCT' THEN - REORDER_QTY
                ELSE 0
           END) AS TOTAL_BALANCE_QTY
FROM INVENTORY_HISTORY
GROUP BY PRODUCTID
ORDER BY MYTYPE ASC

it work fine until i add where clause like this:

WHERE STOREID='6' AND ENTERED_DATE BETWEEN '2014-09-15' AND '2014-09-30'

Problem:

i will get negative value for TOTAL_BALANCE_QTY because it sum all the IN_QTY and deduct the OUT_QTY, but the total of IN_QTY is less than OUT_QTY. How do i avoid the negative value? without where clause, i got no issue, because it sum all the IN_QTY from beginning of time. any suggestion?

enter image description here

Schema:

CREATE TABLE `inventory_history` (
  `INVHID` int(11) NOT NULL AUTO_INCREMENT,
  `INVENTORYID` int(11) DEFAULT NULL,
  `STOREID` int(11) DEFAULT NULL,
  `PRODUCTID` int(11) DEFAULT NULL,
  `UNIT` char(30) DEFAULT NULL,
  `QTY_BALANCE` decimal(10,2) DEFAULT NULL,
  `UNIT_PRICE` decimal(12,4) DEFAULT NULL,
  `REORDER_QTY` decimal(10,2) DEFAULT NULL,
  `MYTYPE` char(15) DEFAULT NULL,
  `ADJUST_TYPE` char(10) DEFAULT NULL,
  `TRANSC_ID` int(11) DEFAULT NULL,
  `ENTERED_BY` int(11) DEFAULT NULL,
  `ENTERED_DATE` datetime DEFAULT NULL,
  `MODIFIED_BY` int(11) DEFAULT NULL,
  `MODIFIED_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`INVHID`)
) ENGINE=InnoDB AUTO_INCREMENT=2142 DEFAULT CHARSET=utf8

SQL data: Dropbox Link (not able to put to sqlfiddle due to char 8000 limit)

Any suggestion/help would be great.

Upvotes: 0

Views: 277

Answers (1)

John Ruddell
John Ruddell

Reputation: 25842

SELECT PRODUCTID, MYTYPE, UNIT, IN_QTY, OUT_QTY, IF(TOTAL_BALANCE_QTY < 0, 0.00, TOTAL_BALANCE_QTY) AS TOTAL_BALANCE_QTY
FROM
(    SELECT PRODUCTID, UCASE(MYTYPE) AS MYTYPE, UNIT,
            SUM(CASE WHEN ADJUST_TYPE = 'ADD' THEN REORDER_QTY ELSE 0 END) AS IN_QTY,
            SUM(CASE WHEN ADJUST_TYPE = 'DEDUCT' THEN REORDER_QTY ELSE 0 END) AS OUT_QTY,
            SUM(CASE WHEN ADJUST_TYPE = 'ADD' THEN REORDER_QTY
                     WHEN ADJUST_TYPE = 'DEDUCT' THEN - REORDER_QTY
                     ELSE 0
                END
            ) AS TOTAL_BALANCE_QTY
    FROM INVENTORY_HISTORY
    GROUP BY PRODUCTID
    ORDER BY MYTYPE ASC
)temp

a better working example would be to use GREATEST() with a default value so you dont need the outer query

SELECT PRODUCTID, UCASE(MYTYPE) AS MYTYPE, UNIT,
       SUM(CASE WHEN ADJUST_TYPE = 'ADD' THEN REORDER_QTY ELSE 0 END) AS IN_QTY,
       SUM(CASE WHEN ADJUST_TYPE = 'DEDUCT' THEN REORDER_QTY ELSE 0 END) AS OUT_QTY,
       GREATEST(SUM(CASE WHEN ADJUST_TYPE = 'ADD' THEN REORDER_QTY
                         WHEN ADJUST_TYPE = 'DEDUCT' THEN - REORDER_QTY
                         ELSE 0
                    END), 0
       ) AS TOTAL_BALANCE_QTY
FROM INVENTORY_HISTORY
GROUP BY PRODUCTID
ORDER BY MYTYPE ASC

Upvotes: 2

Related Questions