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?
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: [email protected]
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
