Do some Addition Function with SQL with null values without make it 0

Question

I do some complicated addition Math in SQL :

Select CAST((CAST(((COUNT(CASE WHEN CONVERT(varchar(5), ReportDate, 108) <= '12:59' AND CLOSE_BY <> '' AND CONVERT(varchar(5), closedate, 108) <= '15:00' THEN CLOSE_BY ELSE NULL END) * 100)/nullif(COUNT(CASE WHEN CONVERT(varchar(5), ReportDate, 108) <= '12:59' THEN ReportDate ELSE NULL END),0)) as decimal(18,2))+ 
CAST(((COUNT(CASE WHEN CONVERT(varchar(5), ReportDate, 108) between '13:00' and '14:59' AND CLOSE_BY <> '' AND CONVERT(varchar(5), closedate, 108) <= '17:00' THEN CLOSE_BY ELSE NULL END) * 100)/nullif(COUNT(CASE WHEN CONVERT(varchar(5), ReportDate, 108) between '13:00' and '14:59' THEN ReportDate ELSE NULL END),0)) as decimal(18,2))+
CAST(((COUNT(CASE WHEN CONVERT(varchar(5), ReportDate, 108) between '15:00' and '16:59' AND CLOSE_BY <> '' AND CONVERT(varchar(5), closedate, 108) <= '19:00' THEN CLOSE_BY ELSE NULL END) * 100)/nullif(COUNT(CASE WHEN CONVERT(varchar(5), ReportDate, 108) between '15:00' and '16:59' THEN ReportDate ELSE NULL END),0)) as decimal(18,2))+ 
CAST(((COUNT(CASE WHEN CONVERT(varchar(5), ReportDate, 108) between '17:00' and '18:59' AND CLOSE_BY <> '' AND CONVERT(varchar(5), closedate, 108) <= '21:00' THEN CLOSE_BY ELSE NULL END) * 100)/nullif(COUNT(CASE WHEN CONVERT(varchar(5), ReportDate, 108) between '17:00' and '18:59' THEN ReportDate ELSE NULL END),0)) as decimal(18,2))+ 
CAST(((COUNT(CASE WHEN CONVERT(varchar(5), ReportDate, 108)  >= '19:00' AND CLOSE_BY <> '' AND CONVERT(varchar(5), closedate, 108)  <= '23:59'  THEN CLOSE_BY ELSE NULL END) * 100)/nullif(COUNT(CASE WHEN CONVERT(varchar(5), ReportDate, 108) >= '18:00'  THEN ReportDate ELSE NULL END),0)) as decimal(18,2))) / 5  as decimal(18,2)) as Avg,  
 getdate() as generatedate FROM WO  
 WHERE contractor is not null and 
 contractor <> 'SYSTEM DEFAULT' and LEFT(SCA,1)<>'L' and LEFT(SCA,1)<>' '  
 and SCH_DATE between ( SELECT CONVERT(DATE , GETDATE() , 103)) and ( SELECT CONVERT(DATE , GETDATE() , 103))  GROUP BY CONTRACTOR,Branch order by CONTRACTOR asc

some of the value has Null value, so the addition won't work , and it became null. I want still process the addition without make the null to 0 , is it possible ?

Arithmetic operation on NULL returns NULL, so if you want to return a value and ignore null, you will have to replace NULL to 0. — user353gre3
– user353gre3, Commented Apr 3, 2014 at 2:10
you cannot add something that undefined to a number... Math doesn't recognize this. Null + 1 = ? do you know the answer? — cyan
– cyan, Commented Apr 3, 2014 at 2:12
so I should change the logic here, to make the query only process something not null, is it AVG in tsql avoid the null ? am I possible to use AVG with my query ? — Sabilv
– Sabilv, Commented Apr 3, 2014 at 2:19

user353gre3 · Accepted Answer · 2014-04-03 02:12:50Z

1

Arithmetic operations on NULL returns NULL, so if you want to return a value and ignore NULL, you will have to replace NULL to 0. Using ISNULL standard SQL function (example: ISNULL(<expression>, 0)) can replace NULL with 0.

answered Apr 3, 2014 at 2:12

user353gre3

2,7554 gold badges26 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Do some Addition Function with SQL with null values without make it 0

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related