SQLite multiple CASE WHEN weird result-CodePudding

I'm using multiple CASE WHEN to find device actions in selected days, but instead of getting only the abreviation names (like V or C), sometimes i get the full action name. If i try to replace the 'ELSE action' with ELSE '', i get some blanks, even though there aren't any blank actions... How can i improve my query?

SELECT device,
           CASE 
           WHEN action='Vaccum' AND strftime('%d', timestamp_action) = '25' THEN 'V' 
           WHEN action='Cooling' AND strftime('%d', timestamp_action) = '25' THEN 'C' ELSE action END AS '25',
    
           CASE 
           WHEN action='Vaccum' AND strftime('%d', timestamp_action) = '26' THEN 'V' 
           WHEN action='Cooling' AND strftime('%d', timestamp_action) = '26' THEN 'C' ELSE action END AS '26',
    
           FROM diary WHERE strftime('%m', timestamp_action = '08')
           GROUP BY device
           ORDER BY device

I want to get the latest action on selected days of all devices. I have around 100 devices and i need the actions for the entire month.

Example table:

    timestamp_action    |   device      |   action
------------------------ --------------- -----------
    2022-08-25 11:08    |      1        |   Cooling
    2022-08-25 11:09    |      1        |   Vaccum
    2022-08-25 11:08    |      2        |   Cooling
    2022-08-26 11:10    |      2        |   Vaccum
    2022-08-26 11:11    |      2        |   Cooling
    2022-08-26 12:30    |      1        |   Vaccum

So the result i'm looking for is:

    device |    25     | 26 .....
----------- ----------- --------------
    1      |    V      |  V
    2      |    C      |  C

CodePudding user response：

Use 2 levels of aggregation:

WITH cte AS (
  SELECT device, 
         strftime('%d', timestamp_action) day,
         CASE action WHEN 'Vaccum' THEN 'V' WHEN 'Cooling' THEN 'C' ELSE action END action,
         MAX(timestamp_action) max_timestamp_action
  FROM diary
  WHERE strftime('%Y-%m', timestamp_action) = '2022-08'
  GROUP BY device, day
)  
SELECT device,
       MAX(CASE WHEN day = '25' THEN action END) `25`,
       MAX(CASE WHEN day = '26' THEN action END) `26`
FROM cte
GROUP BY device;

See the demo.