← Back to notes

Final SQL to Return Painting Status Data

Final SQL to Return Painting Status Data.md

Final SQL to Return Painting Status Data

SELECT 
    painting_title,
    MIN(the_date) AS Start_Date,
    MAX(CASE WHEN painting_status = 'completed' THEN the_date END) AS End_Date,
    COUNT(DISTINCT the_date) AS Total_Days_Worked,
    SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) AS Total_Duration
FROM 
    time_data
WHERE 
    painting_title = ? -- Use your parameter placeholder for the painting title
GROUP BY 
    painting_title;