Multiple SQL case statements

Ignition
,
1

I am writing a SQL query that counts all of the errors that we logged between 2 dates. Unfortunately, some of the data in the table we are pulling from was logged incorrectly. To fix this, I tried to add some CASE statements into the SQL query to change the error name if it was logged before a specific date. The query I wrote works as intended, but it just seems to be a lot of CASE statements. Here is the code:

SELECT 
		
		case

		when  :Startdate <= '2025-6-13 00:00:00' and Fault = 'Inline fill timed out' or  :Enddate <= '2025-6-13 00:00:0' and Fault = 'Inline fill timed out' then 'EOAT Mandrel Stripper Extend'
		when  :Startdate <= '2025-6-13 00:00:00' and Fault = 'Expected part present at shuttle' or  :Enddate <= '2025-6-13 00:00:0' and Fault = 'Expected part present at shuttle' then 'EOAT Mandrel Stripper Retract'
		when  :Startdate <= '2025-6-13 00:00:00' and Fault = 'Escapement slide 1 (upper) extend' or  :Enddate <= '2025-6-13 00:00:0' and Fault = 'Escapement slide 1 (upper) extend' then 'Inline fill timed out'
		when  :Startdate <= '2025-6-13 00:00:00' and Fault = 'Escapement slide 1 (upper) retract' or  :Enddate <= '2025-6-13 00:00:0' and Fault = 'Escapement slide 1 (upper) retract' then 'Expected part present at shuttle'
		when  :Startdate <= '2025-6-13 00:00:00' and Fault = 'Escapement slide 2 (lower) extend' or  :Enddate <= '2025-6-13 00:00:0' and Fault = 'Escapement slide 2 (lower) extend' then 'Escapement slide 1 (upper) extend'
		when  :Startdate <= '2025-6-13 00:00:00' and Fault = 'Escapement slide 2 (lower) retract' or  :Enddate <= '2025-6-13 00:00:0' and Fault = 'Escapement slide 2 (lower) retract' then 'Escapement slide 1 (upper) retract'
		when  :Startdate <= '2025-6-13 00:00:00' and Fault = 'Escapement part eject extend' or  :Enddate <= '2025-6-13 00:00:0' and Fault = 'Escapement part eject extend' then 'Escapement slide 2 (lower) extend'
		when  :Startdate <= '2025-6-13 00:00:00' and Fault = 'Escapement paret eject retract' or  :Enddate <= '2025-6-13 00:00:0' and Fault = 'Escapement paret eject retract' then 'Escapement slide 2 (lower) retract'
		when  :Startdate <= '2025-6-13 00:00:00' and Fault = 'Escapement part containment extend' or  :Enddate <= '2025-6-13 00:00:0' and Fault = 'Escapement part containment extend' then 'Escapement part eject extend'
		when  :Startdate <= '2025-6-13 00:00:00' and Fault = 'Escapement part containment retract' or  :Enddate <= '2025-6-13 00:00:0' and Fault = 'Escapement part containment retract' then 'Escapement paret eject retract'
		when  :Startdate <= '2025-6-13 00:00:00' and Fault = 'IV_1 sensor timed out' or  :Enddate <= '2025-6-13 00:00:0' and Fault = 'IV_1 sensor timed out' then 'Escapement part singulator extend'

		
		else Fault
		end as Fault,
		count(Fault) as count

FROM (SELECT Datetime,
		MachineID,
		Fault
		
		FROM AutomatedEquipmentFaultLog

WHERE MachineID = :MachineID  and Datetime BETWEEN  :Startdate and  :Enddate and Fault !=  'Bowl low parts' and  Fault != 'Inline fill timed out')q
GROUP BY Fault
ORDER BY count desc

I also want to avoid switching the actual table values if possible.
Is there a better way to write this query?

2

As in you want to avoid fixing the source data? That would be the obvious thing to do, to make querying it easier in general and not impact query performance

3

Yeah, I just want a quick fix for the time being. The long term will be to fix the source data.

4

There is no need for the sub query, and you can consolidate the date checking in the case.

SELECT 
    CASE 
        WHEN (:Startdate <= '2025-06-13' OR :Enddate <= '2025-06-13') THEN
            CASE Fault
                WHEN 'Inline fill timed out' THEN 'EOAT Mandrel Stripper Extend'
                WHEN 'Expected part present at shuttle' THEN 'EOAT Mandrel Stripper Retract'
                WHEN 'Escapement slide 1 (upper) extend' THEN 'Inline fill timed out'
                WHEN 'Escapement slide 1 (upper) retract' THEN 'Expected part present at shuttle'
                WHEN 'Escapement slide 2 (lower) extend' THEN 'Escapement slide 1 (upper) extend'
                WHEN 'Escapement slide 2 (lower) retract' THEN 'Escapement slide 1 (upper) retract'
                WHEN 'Escapement part eject extend' THEN 'Escapement slide 2 (lower) extend'
                WHEN 'Escapement paret eject retract' THEN 'Escapement slide 2 (lower) retract'
                WHEN 'Escapement part containment extend' THEN 'Escapement part eject extend'
                WHEN 'Escapement part containment retract' THEN 'Escapement paret eject retract'
                WHEN 'IV_1 sensor timed out' THEN 'Escapement part singulator extend'
                ELSE Fault
            END
        ELSE Fault
    END AS Fault,
    COUNT(Fault) AS count
FROM AutomatedEquipmentFaultLog
WHERE MachineID = :MachineID 
    AND Datetime BETWEEN :Startdate AND :Enddate 
    AND Fault NOT IN ('Bowl low parts', 'Inline fill timed out')
GROUP BY Fault
ORDER BY count DESC
2 Likes
5

Gave this a try and it seemed to work, and looks much cleaner. Thanks!

Is there any other ways I could optimize this query? Just trying to learn from my mistakes.

6

Not really.

I would set this up as an update query and fix the bad entries with a single execution. Though, my priority would be fixing the source data asap.

2 Likes