CODEWITHSUNDEEP
sqlsql-server-2005
Baz
Baz

Reputation: 13145

Conditional row subtraction

I'm using sql server 2005.

I have two columns a and b.

I wish to subtract b from a to produce c, which I do as follows:

a - b as c

but I want to complement this by having c = 0 if a or b is 0. How do I do this?

Thanks,

Barry

Upvotes: 1

Views: 1466

Answers (5)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

Using a CASE statement is definitly the most recommended way but for fun and pleasure, using some bit shifting would work as well

c = ABS(a - b) 
    * (((CAST(ABS(a * b) AS BIGINT)+0x7FFFFFFF)) 
    / POWER(2, 16) / POWER(2, 15))

Steps taken

  1. Multiply to get either a zero (a or b is zero) or non-zero (a and b both <> 0) value. The non-zero value needs to get converted to 1

    ABS(a * b)

  2. Cast to BIGINT to prevent Arithmetic overflow

    CAST(ABS(a * b) AS BIGINT)

  3. Make sure bit 32 is set to 1 if ABS(a * b) was a non-zero value.

    CAST(ABS(a * b) AS BIGINT)+0x7FFFFFFF

  4. Shift 31 bits retaining either a 0 or 1. (Because POWER returns an int this has to be done in two steps instead of a simpler POWER(2, 31))

    ((CAST(ABS(a * b) AS BIGINT)+0x7FFFFFFF))
    / POWER(2, 16) / POWER(2, 15)

  5. Multiply the original equation with our calculated 0 or 1.

    ABS(a - b)
    * (((CAST(ABS(a * b) AS BIGINT)+0x7FFFFFFF))
    / POWER(2, 16) / POWER(2, 15))

Test script

;WITH q (a, b) AS (
  SELECT * FROM (VALUES
      (0, 0)
    , (0, 1)
    , (0, 2)
    , (1, 0)
    , (1, 1)
    , (1, 2)
    , (2, 0)
    , (2, 1)
    , (2, 2)
    , (9, 0)
    , (9, 1)
    , (9, 2)
  ) a (b, c)
)
SELECT  a
        , b
        , c = ABS(a - b) 
              * (((CAST(ABS(a * b) AS BIGINT)+0x7FFFFFFF)) 
              / POWER(2, 16) / POWER(2, 15))
FROM    q

Edit

As in comments we wondered about performance differences, following is a quick test setup.

Performance test setup

CREATE TABLE q (a INTEGER, b INTEGER)
;WITH numbers (a) AS (
    SELECT  0
    UNION ALL 
    SELECT  a + 1
    FROM    numbers
    WHERE   a < 999
)
INSERT  INTO q
SELECT  a1.a, a2.a
FROM    numbers a1
        CROSS APPLY numbers a2
OPTION (MAXRECURSION 0)

Performance Test

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT  c = (a - b)
              * (((CAST(ABS(a * b) AS BIGINT)+0x7FFFFFFF)) 
              / POWER(2, 16) / POWER(2, 15))
FROM    q

SELECT 
   CASE WHEN a = 0 OR b = 0 
        THEN 0 
        ELSE a - b 
   END c
FROM    q

Upvotes: 2

onedaywhen
onedaywhen

Reputation: 57063

SELECT a, b,    
       COALESCE(NULLIF(a, 0) - NULLIF(b, 0), 0) AS c
  FROM T1;

Or

SELECT a, b, 0 AS c
  FROM T1
 WHERE 0 IN (a, b)
UNION
SELECT a, b, a - b AS c
  FROM T1
 WHERE 0 NOT IN (a, b);

Note each handles nulls differently (you spec does not mention how to handle nulls).

Upvotes: 0

Mithrandir
Mithrandir

Reputation: 25377

Try his:

 SELECT 
 a,b,
 CASE
    WHEN a = 0 OR  b = 0 THEN 0
    ELSE
       a - b
 END AS c
 FROM table

Upvotes: 0

hgulyan
hgulyan

Reputation: 8249

Try using case

SELECT 
   CASE WHEN a = 0 OR b = 0 
        THEN 0 
        ELSE a - b 
   END c

More on this

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Upvotes: 3

Dimi Takis
Dimi Takis

Reputation: 4959

Well, this should do it :)

SELECT
 CASE WHEN A=0 OR B=0 THEN 0 ELSE A-B END AS C
FROM 
 TABLE

Upvotes: 0

Related Questions