Case When Column Greater then Zero or Null then Calculate else 0

HI Experts !
The below Sql statement retrieve results ok when both column has values > 0 , but if the column1 has null or 0 values and column2 has value 25 . It calculate results 3.125.
Requires not calculate any thing if both columns has zero /null values or any of the columns ( column1 and column2) has zero/null value.

(7*( 0- 32)*5/9 +( 25- 32)*5/9)/8 =3.125

SELECT  
       [startdate]= Startdate,
[Column] = MAX(CASE WHEN Itemname='XYZ'    and Column1>0  and Column2>0 THEN (7*( Column1- 32)*5/9 +( Column2- 32)*5/9)/8*   END)     
From Table
Group by Stardate

Your query does not work. Based on some sample data, this works

Create table #t (StartDate date,
				Column1 int,
				Column2 int,
				ItemName varchar(10))

insert into #t values
('1/1/2020', 0 , 7, 'XYZ'),
('1/1/2020', 0 , null, 'XYZ'),
('1/1/2020', null , 7, 'XYZ'),
('1/1/2020', 50 , 0, 'XYZ'),
('1/1/2020', 44 , 33, 'XYZ'),
('1/1/2020', 11 , 66, 'ABC')


SELECT  
       [startdate]= Startdate,
	[Column1] = MAX(CASE WHEN Itemname='XYZ'    and Column1>0  and Column2>0 THEN 
						Column1 --(7*( Column1- 32)*5/9 +( Column2- 32)*5/9)/8   
					END) ,
	[Column2] = MAX(CASE WHEN Itemname='XYZ'    and Column1>0  and Column2>0 THEN 
						Column2 --(7*( Column1- 32)*5/9 +( Column2- 32)*5/9)/8   
					END)     
					    
From #t
Group by Startdate
2 Likes

Hi mike!
Based on sample data it works .. The issue was i found the values 0 in column1 or Column2 in temperature degree C and the storage unit of database Fahrenheit . If we put 0 values as Centigrade it means 32 Fahrenheit. Thats why while converting the F to C ( Column1- 32)*5/9 it retrieves some values.Capture
Capture1

Many thanks for your reply it was my mistake