[DUG] Epiphany

[Neven MacEwan]

Hi

Yeah ok this is off-topic but I thought I'd share, and a lot of us do db 
work

Its always aggravated me doing percentages or ratios in SQL because of 
the possiblility of a div 0 error
(you can wrap an if or case around it sure) but if you are doing this on 
a aggregate/group by it gets really ugly

So....I had an epiphany moment..what does  X/NULL return answer NULL! so 
the addition of a NULLIF() and a COALESCE() and you have it

ie

  SELECT LEFT(
    'Past 12 Months, 1st Half $'+CONVERT(VARCHAR(12),CONVERT(MONEY,SUM(CASE
      WHEN TranDate < GETDATE()-183 THEN ((DebitCredit*2)-1)*Amount
      ELSE 0
    END))) + ' -> 2nd Half $' +
    CONVERT(VARCHAR(12),CONVERT(MONEY, SUM(CASE
          WHEN TranDate < GETDATE()-183 THEN 0
          ELSE ((DebitCredit*2)-1)*Amount
       END)))
 + ' (Trend '+COALESCE(CONVERT(VARCHAR(12),
       FLOOR(
         (SUM(
           CASE
            WHEN TranDate < GETDATE()-183 THEN 0
            ELSE ((DebitCredit*2)-1)*Amount    
           END) -
         SUM(CASE
           WHEN TranDate < GETDATE()-183 THEN ((DebitCredit*2)-1)*Amount
           ELSE 0
         END))*100/NULLIF(
      (SUM(
        CASE
          WHEN TranDate < GETDATE()-183 THEN ((DebitCredit*2)-1)*Amount
         ELSE 0
        END)),0)
      ))+'%)','TBA)'),255)  AS Data
  FROM DB_Trans a
  WHERE
    a.TranDate >= GETDATE()-366 AND
    a.Source = 0
  GROUP BY  
    a.DebtorID

Any simpler solutions?