StudioTime Progress

Initial/base Query for Caption

SELECT painting_title,
       MIN(the_date) AS start_date,
       MAX(the_date) AS end_date,
       'completed' AS status, -- We assume that if we're querying, the painting process is completed
       DATEDIFF(MAX(the_date), MIN(the_date)) + 1 AS total_days_worked, -- Calculate the difference in days
       SUM(duration) AS total_duration
FROM time_data
WHERE painting_title = 'YourSelectedPaintingTitle' -- Replace with your actual title
AND painting_status IN ('start', 'completed') -- Only consider rows with 'start' or 'completed' status
GROUP BY painting_title;

Updated Query for Caption

SELECT painting_title,
       MIN(the_date) AS start_date,
       CASE
           WHEN MAX(CASE WHEN painting_status = 'completed' THEN the_date END) IS NULL
           THEN MAX(CASE WHEN painting_status = 'in progress' THEN the_date END)
           ELSE MAX(CASE WHEN painting_status = 'completed' THEN the_date END)
       END AS end_date,
       CASE
           WHEN MAX(CASE WHEN painting_status = 'completed' THEN the_date END) IS NULL
           THEN 'in progress'
           ELSE 'completed'
       END AS status,
       DATEDIFF(
           CASE
               WHEN MAX(CASE WHEN painting_status = 'completed' THEN the_date END) IS NULL
               THEN MAX(CASE WHEN painting_status = 'in progress' THEN the_date END)
               ELSE MAX(CASE WHEN painting_status = 'completed' THEN the_date END)
           END, 
           MIN(the_date)
       ) + 1 AS total_days_worked,
       SUM(duration) AS total_duration
FROM time_data
WHERE painting_title = 'YourSelectedPaintingTitle' -- Replace with your actual title
AND painting_status IN ('start', 'in progress', 'completed') -- Consider all relevant statuses
GROUP BY painting_title;

Updated query for caption 2/27/24

SELECT
    painting_title,
    the_date AS progress_date,
    painting_status AS status,
    image,
    duration
FROM
    time_data
WHERE
    painting_title = ? AND painting_status IN ('start', 'in progress', 'completed')
ORDER BY
    the_date;