SQL Query from Expression

Archived Topics
1

I have a SQL query to calculate the production rate of a machine based on the count now and the count an hour ago. The trouble is that it errors out if there is now data or if the query returns a divide by zero. I need to encapsulate the query within an expression or script to test the values ahead of time or provide a failover. The query is as follows:

Select CAST((max(Count)-min(Count)) / (DatePart(mi,max(t_stamp) - min(t_stamp))) AS DECIMAL(5,2)) as "BPM" FROM Case_Inkjets WHERE EquipID = {Root Container.P0248.EquipID} and t_stamp > DateAdd(hh,-1,GetDate()) and Count > 0

What do you suggest?

2

To add more logic into the query:

Select COALESCE(CAST(CASE WHEN (DatePart(mi,max(t_stamp) - min(t_stamp))) <= 0 THEN 0 ELSE (max(Count)-min(Count)) / (DatePart(mi,max(t_stamp) - min(t_stamp))) END AS DECIMAL(5,2)),0) as "BPM" FROM Case_Inkjets WHERE EquipID = {Root Container.P0248.EquipID} and t_stamp > DateAdd(hh,-1,GetDate()) and Count > 0
Try this out, it detects when you divide by zero and if no rows exist it can coalesce the value to 0.

3

That did the trick, thanks! :smiley: