Handling nulls and percentages

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

SQL Server 2000 Forums

SQL Server Development (2000)

Handling nulls and percentages

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-05-09 : 09:55:46

How can I handle nulls and percentages? I need to do some calculations for each customer, but not all customers will have data.

This statement would return the total records and the ones that are 'LATE'. Now, I need to calculate the percentage that are late. If I do IsNull and set to 0, then I will get a divide by 0 error, make sense? (Late/TotalRecords) * 100 As PercentLate

SELECT SUM(CASE WHEN DATERECEIVED BETWEEN
@STARTDATE AND @ENDDATE
THEN 1 ELSE 0 END) AS TotalRecords,
SUM(CASE WHEN DATERECEIVED BETWEEN
@STARTDATE AND @ENDDATE
AND Status = 'LATE'
THEN 1 ELSE 0 END) AS Late

FROM Table
WHERE Customer = @Customer

Kristen
Test

22859 Posts

Posted - 2006-05-09 : 10:07:11

I'l tackle it this way:


CASE WHEN TotalRecords = 0 
       THEN NULL -- Could use 0 here if you like
       ELSE (Late/TotalRecords) * 100 END  As PercentLate

Kristen

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-05-09 : 12:51:49

It gives me invalid column name 'TotalRecords'

Can I use it in that manner since it's an alias?

Kristen
Test

22859 Posts

Posted - 2006-05-09 : 13:04:48

"Can I use it in that manner since it's an alias?"

Sort-of


SELECT TotalRecords, 
       Late,
       CASE WHEN TotalRecords = 0 
           THEN NULL -- Could use 0 here if you like
           ELSE (Late/TotalRecords) * 100 
           END  As PercentLate
FROM
(
SELECT SUM(CASE WHEN DATERECEIVED BETWEEN 
@STARTDATE AND @ENDDATE
THEN 1 ELSE 0 END) AS TotalRecords,
SUM(CASE WHEN DATERECEIVED BETWEEN 
@STARTDATE AND @ENDDATE
AND Status = 'LATE'
THEN 1 ELSE 0 END) AS Late

FROM Table
WHERE Customer = @Customer
) AS X

Kristen

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-05-09 : 13:10:23

Thanks Kristen! That's what I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources