SQLize Online / PHPize Online  /  SQLtest Online

WITH RentalDetails AS ( SELECT f.title, r.rental_date, r.return_date, p.payment_date, f.rental_rate, -- Количество дней задержки (если есть) DATEDIFF(r.return_date, r.rental_date) AS lateness_days, (DATEDIFF(r.return_date, r.rental_date)) AS lateness_penalty, -- Общая сумма включает как ставку аренды, так и штраф (f.rental_rate + CASE WHEN DATEDIFF(r.return_date, r.rental_date) > 0 THEN DATEDIFF(r.return_date, r.rental_date) ELSE 0 END) AS amount FROM rental r JOIN inventory i ON r.inventory_id = i.inventory_id JOIN film f ON i.film_id = f.film_id JOIN customer c ON r.customer_id = c.customer_id JOIN payment p ON r.rental_id = p.rental_id WHERE c.first_name = 'DOROTHY' AND c.last_name = 'TAYLOR' AND p.payment_date BETWEEN '2005-08-01' AND '2005-08-31' ) SELECT ROW_NUMBER() OVER (ORDER BY rental_date) AS row_num, title, rental_date, return_date, payment_date, rental_rate, lateness_penalty AS lateness_days, -- Количество дней задержки amount FROM RentalDetails UNION ALL SELECT 'Total' AS row_num, NULL AS title, NULL AS rental_date, NULL AS return_date, NULL AS payment_date, NULL AS rental_rate, SUM(lateness_penalty) AS lateness_days, -- Общее количество дней задержки SUM(amount) AS amount FROM RentalDetails; ; show status like 'Last_query_cost';