ChatGPT Temporary Reference

ChatGPT Temporary Reference

Query Update

SELECT 
  td1.painting_title,
  td1.the_date AS session_date,
  td1.image,
  td1.duration,
  agg.start_date,
  agg.completion_date,
  (DATEDIFF(IFNULL(agg.completion_date, CURDATE()), agg.start_date) + 1) AS total_days_worked,
  agg.total_duration
FROM 
  time_data td1
JOIN (
  SELECT 
    painting_title,
    MIN(the_date) AS start_date,
    MAX(CASE WHEN painting_status = 'completed' THEN the_date END) AS completion_date,
    SUM(duration) AS total_duration
  FROM 
    time_data
  WHERE 
    painting_title = ? 
    AND painting_status IN ('start', 'in progress', 'completed')
  GROUP BY 
    painting_title
) AS agg ON td1.painting_title = agg.painting_title
WHERE 
  td1.painting_title = ? 
  AND td1.painting_status IN ('start', 'in progress', 'completed')
  AND td1.image IS NOT NULL
ORDER BY 
  td1.the_date, td1.entry;