SQL calculation

Try this:

SELECT Qty * iif(QTY <> 0 AND TRANSTYPE='ADJ' ,-1,1) as QtyAdjusted, TransType ....

Will these adjust the existing QTY column? I dont want to add a new column, I want to overwrite the existing one.

DECLARE @NewQuantity bigint;

SELECT @NewQuantity = CASE WHEN (QTY <> 0 AND TRANSTYPE='ADJ') then QTY * -1 ELSE QTY END AS QTY from table;

update table
set qty=@NewQuantity
where ---

Scott, this failed, it created a new column 'AS Expr1', it also didnt even put the values into minuses.

BuggyCoder, I'm trying to create a view, can this code be added and run in the view?

DECLARE @NewQuantity bigint;

SELECT @NewQuantity = CASE WHEN (QTY <> 0 AND TRANSTYPE='ADJ') then QTY * -1 ELSE QTY END AS QTY from table;

select @NewQuantity;

BuggyCoder,

This is my code so far, how do I add yours? I cannot have 2 SELECT statements in a view, surely?....



SELECT DISTINCT
                      TOP (100) PERCENT dbo.tblMestecData.idMESTEC, dbo.tblMestecData.MESDATE, dbo.tblMestecData.LOCATION, dbo.tblMestecData.ALLOCATION,
                      dbo.tblMestecData.OWNERSHIPTYPE, dbo.tblMestecData.PARTNBR, dbo.tblMestecData.VERSION, dbo.tblMestecData.PARTDESCRIPTION, dbo.tblMestecData.QTY,
                      dbo.tblMestecData.TRANSTYPE, dbo.tblMestecData.REASONCODE, dbo.tblMestecData.REASONDESC, dbo.tblMestecData.TRANSCOMMENT,
                      dbo.tblMestecData.GRNNBR, dbo.tblMestecData.SERIALNBR, dbo.tblMestecData.CURRENCY, dbo.tblMestecData.UNITCOST,
                      dbo.tblMestecData.TOTALBASELINECOST, dbo.tblMestecData.COMPARTNBR, CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND
                      UNITCOST > 0 THEN Price ELSE V1Price END AS DEFV1PRICE, CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND
                      UNITCOST > 0 THEN Currency ELSE V1Currency END AS V1_CURRENCY, CASE WHEN CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND
                      UNITCOST > 0 THEN Currency ELSE V1Currency END = 'EUR' THEN EUR WHEN CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND
                      UNITCOST > 0 THEN Currency ELSE V1Currency END = 'USD' THEN USD ELSE 0 END AS EX_RATE, CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND
                      UNITCOST = 0 THEN 0 ELSE CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND UNITCOST > 0 THEN Price ELSE V1Price END * QTY END AS MAT_COST,
       
FROM         dbo.tblMestecData LEFT OUTER JOIN
                      dbo.stocksale ON dbo.tblMestecData.PARTNBR = dbo.stocksale.PartNumber LEFT OUTER JOIN
                      dbo.tblV1Price ON dbo.tblMestecData.PARTNBR = dbo.tblV1Price.V1Item CROSS JOIN
                      dbo.tblExRates
WHERE     (dbo.tblMestecData.MESDATE > '2012-01-29') AND (dbo.tblMestecData.MESDATE < '2012-02-25')
ORDER BY dbo.tblMestecData.TRANSTYPE

Mbizup: Error in list of function arguments: '<' not recognized.
Unable to parse query text.

>> Scott, this failed, it created a new column 'AS Expr1', it also didnt even put the values into minuses <<

Something's not right, because it explicilty labeled the column as "QTY".

Maybe you have to use a different name than one that already exists on the table??

You also don't need to check for QTY <> 0, since 0 * -1 will still be zero.



SELECT DISTINCT
                      TOP (100) PERCENT dbo.tblMestecData.idMESTEC, dbo.tblMestecData.MESDATE, dbo.tblMestecData.LOCATION, dbo.tblMestecData.ALLOCATION,
                      dbo.tblMestecData.OWNERSHIPTYPE, dbo.tblMestecData.PARTNBR, dbo.tblMestecData.VERSION, dbo.tblMestecData.PARTDESCRIPTION, dbo.tblMestecData.QTY,
                      dbo.tblMestecData.TRANSTYPE, dbo.tblMestecData.REASONCODE, dbo.tblMestecData.REASONDESC, dbo.tblMestecData.TRANSCOMMENT,
                      dbo.tblMestecData.GRNNBR, dbo.tblMestecData.SERIALNBR, dbo.tblMestecData.CURRENCY, dbo.tblMestecData.UNITCOST,
                      dbo.tblMestecData.TOTALBASELINECOST, dbo.tblMestecData.COMPARTNBR, CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND
                      UNITCOST > 0 THEN Price ELSE V1Price END AS DEFV1PRICE, CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND
                      UNITCOST > 0 THEN Currency ELSE V1Currency END AS V1_CURRENCY, CASE WHEN CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND
                      UNITCOST > 0 THEN Currency ELSE V1Currency END = 'EUR' THEN EUR WHEN CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND
                      UNITCOST > 0 THEN Currency ELSE V1Currency END = 'USD' THEN USD ELSE 0 END AS EX_RATE, CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND
                      UNITCOST = 0 THEN 0 ELSE CASE WHEN OWNERSHIPTYPE = 'VMI Thales' AND UNITCOST > 0 THEN Price ELSE V1Price END * QTY END AS MAT_COST,
        CASE WHEN (TRANSTYPE='ADJ') then QTY * -1 ELSE QTY END AS QTY_NEW

FROM         dbo.tblMestecData LEFT OUTER JOIN
                      dbo.stocksale ON dbo.tblMestecData.PARTNBR = dbo.stocksale.PartNumber LEFT OUTER JOIN
                      dbo.tblV1Price ON dbo.tblMestecData.PARTNBR = dbo.tblV1Price.V1Item CROSS JOIN
                      dbo.tblExRates
WHERE     (dbo.tblMestecData.MESDATE > '2012-01-29') AND (dbo.tblMestecData.MESDATE < '2012-02-25')
ORDER BY dbo.tblMestecData.TRANSTYPE

Thanks Scott, that worked fine.