Zeroise small sums

• Roger Graham

  Newbie

  Points: 3

  More actions

  July 25, 2006 at 9:55 am

  #113898

  We want to be able to set to zero the result of a sum statement if the result is small (say <0.05 , >-0.05)

  select fee_earner_id, sum(fees)

  group by fee_earner_id

  to return

  123, 23.54

  245,  0.00

  345, 35.56

  instead of

  123, 23.54

  245,  0.03

  345, 35.56

  Any idea how this could be done in a query/stored procedure.

  Many thanks

  Roger Graham

• Vasc

  SSCarpal Tunnel

  Points: 4643

  More actions

  July 25, 2006 at 10:05 am

  #651385

  DECLARE @t TABLE(fee_earner_id INT, fees MONEY)

  INSERT INTO @t

  SELECT 123, 23.54 UNION ALL

  SELECT 245,  0.03 UNION ALL

  SELECT 345, -0.46

   

  SELECT fee_earner_id,CASE  WHEN ABS(SUM(fees))<0.5 THEN 0 ELSE SUM(fees) END AS fees

  FROM @t

  GROUP BY fee_earner_id

  ---

  Kindest Regards,

  Vasc