Need SELECT to replace the value in Column A, Row n based on the value in the same row of Column B

I have a SQL script that returns pH and Flow values from two separate tables. I need to modify the script so that when the Flow Rate = 0, the pH value is set to 0. I tried to use a CASE statement, but couldn't figure out how to make it change the pH based on Flow Rate.

SELECT 
   a.t_stamp_hour
   , a.Avg_mbpH0_pH AS pH
   , b.Avg_MB_Eff_FlowC2 AS "Flow Rate"

FROM dbo.comp_V4_Sensors_AS_1_hr AS a

INNER JOIN dbo.comp_C2_10_sec_AS_1_hr AS b
   ON a.t_stamp_hour = b.t_stamp_hour

WHERE (a.t_stamp_hour > DATEADD(MONTH,-1,CAST(GETDATE() AS datetime)))

Open in new window

Example data:

SQLMicrosoft SQL Server

Last Comment

James Steinbacher

8/22/2022 - Mon

slightwv (䄆 Netminder)

3/3/2022

I think I'm missing something here and I apologize if I am but, something like:

SELECT 
   a.t_stamp_hour
case when b.Avg_MB_Eff_FlowC2=0 then 0 else a.Avg_mbpH0_pH AS pH
   , b.Avg_MB_Eff_FlowC2 AS "Flow Rate"
...

Open in new window

James Steinbacher

3/3/2022

ASKER

That's basically what I tried. The current version throws a syntax error:

SELECT 
	a.t_stamp_hour
	, CASE
		WHEN b.Avg_MB_Eff_FlowC2 = 0
		THEN 0
		ELSE a.Avg_mbpH0_pH AS pH
	, b.Avg_MB_Eff_FlowC2 AS "Flow Rate"

FROM dbo.comp_V4_Sensors_AS_1_hr AS a

INNER JOIN dbo.comp_C2_10_sec_AS_1_hr AS b
	ON a.t_stamp_hour = b.t_stamp_hour

WHERE (a.t_stamp_hour > DATEADD(MONTH,-1,CAST(GETDATE() AS datetime)))