Skip to content

Instantly share code, notes, and snippets.

@vladimir-light
Last active January 3, 2016 11:59
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save vladimir-light/8459663 to your computer and use it in GitHub Desktop.
Save vladimir-light/8459663 to your computer and use it in GitHub Desktop.
Sakila re-calculating paiment amount (by days and hours)
SELECT i.inventory_id,
f.title,
@rental_duration_in_days := f.rental_duration as 'rental_duration_in_DAYS',
@rental_duration_in_hours := (@rental_duration_in_days * 24) as 'rental_duration_in_HOURS',
f.rental_rate,
p.amount as 'amount_paid',
r.rental_date,
r.return_date,
@total_days_rented := DATEDIFF(r.return_date, r.rental_date) AS 'total_DAYS_rented',
@rental_ratio_days := CAST((@total_days_rented / @rental_duration_in_days) AS DECIMAL(11,4)) AS 'rental_ratio_by_DAYS',
CAST((@rental_ratio_days * f.rental_rate) as decimal(11,4)) as 'calculated_amount_by_DAYS',
@total_hours_rented := TIMESTAMPDIFF(HOUR,r.rental_date,r.return_date) AS 'total_HOURS_rented',
@rental_ratio_hours := CAST((@total_hours_rented / @rental_duration_in_hours) AS DECIMAL(11,4)) AS 'rental_ratio_by_HOURS',
CAST((@rental_ratio_hours * f.rental_rate) as decimal(11,4)) as 'calculated_amount_by_HOURS'
FROM payment AS p
JOIN rental AS r ON(p.rental_id = r.rental_id AND r.customer_id = 187 AND DATE(r.rental_date) = '2005-08-18')
JOIN inventory AS i ON(r.inventory_id = i.inventory_id)
JOIN film AS f ON(i.film_id = f.film_id)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment