calculate percentage in sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

calculate percentage in sql

Author

Topic

hizakemi
Starting Member

33 Posts

Posted - 2005-12-13 : 07:49:01

Hi, Please help.

I need to calculate percentage [COUNT(ALERT_RECEIVED_DATE) FRAUDCT,over SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK] I tried my best but I cant come up with the solution.

Please, help.

Thk

SELECT
CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101) PERIOD,
COUNT(ALERT_RECEIVED_DATE) FRAUDCT,
SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK,
SUM(CASE WHEN FRAUD_DECISION ='D' THEN 1 ELSE 0 END) DECLINED,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) PENDING_EXCEPTION,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION='D' THEN 1 ELSE 0 END) DECLINED_EXCEPTION
FROM
TBLFRAUDFINDER O

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-13 : 08:05:52

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 08:14:19

Something like this perhaps?


SELECT
CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101) PERIOD,
COUNT(ALERT_RECEIVED_DATE) FRAUDCT,
SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK,
SUM(CASE WHEN FRAUD_DECISION ='D' THEN 1 ELSE 0 END) DECLINED,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) PENDING_EXCEPTION,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION='D' THEN 1 ELSE 0 END) DECLINED_EXCEPTION
, (COUNT(ALERT_RECEIVED_DATE) * 100.0) / SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) AS MyPercentage
FROM TBLFRAUDFINDER O
GROUP BY CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101)

Edit: Probably needs some code to work around the situation where "SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END)" is zero, and generates a "Divide by zero" error

Probably:
NullIf(SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END), 0)
would do!

Kristen

hizakemi
Starting Member

33 Posts

Posted - 2005-12-13 : 08:44:38

Thank you Kristen
It works very well!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources