public.get_customer_balance
Documentation | Pagila |
Schema | public |
Name | get_customer_balance |
Module | Functions |
The get_customer_balance returns the current amount owing on a specified customer's account.
Input/Output
Mode | Name | Data type | Description | |
---|---|---|---|---|
1 | OUT | Returns | numeric | Returns the amount owing on the customer's account. |
Returns the amount owing on the customer's account. | ||||
2 | IN | p_customer_id | integer | The ID of the customer to check, from the customer_id column of the customer table. |
The ID of the customer to check, from the customer_id column of the customer table. | ||||
3 | IN | p_effective_date | timestamp with time zone | The cutoff date for items that will be applied to the balance. Any rentals, payments, and so forth after this date are not counted. |
The cutoff date for items that will be applied to the balance. Any rentals, payments, and so forth after this date are not counted. |
Script
CREATE OR REPLACE FUNCTION public.get_customer_balance(p_customer_id integer, p_effective_date timestamp without time zone) RETURNS numeric LANGUAGE plpgsql AS $function$ --#OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE --#THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS: --# 1) RENTAL FEES FOR ALL PREVIOUS RENTALS --# 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE --# 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST --# 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED DECLARE v_rentfees DECIMAL(5,2); --#FEES PAID TO RENT THE VIDEOS INITIALLY v_overfees INTEGER; --#LATE FEES FOR PRIOR RENTALS v_payments DECIMAL(5,2); --#SUM OF PAYMENTS MADE PREVIOUSLY BEGIN SELECT COALESCE(SUM(film.rental_rate),0) INTO v_rentfees FROM film, inventory, rental WHERE film.film_id = inventory.film_id AND inventory.inventory_id = rental.inventory_id AND rental.rental_date <= p_effective_date AND rental.customer_id = p_customer_id; SELECT COALESCE(SUM(IF((rental.return_date - rental.rental_date) > (film.rental_duration * '1 day'::interval), ((rental.return_date - rental.rental_date) - (film.rental_duration * '1 day'::interval)),0)),0) INTO v_overfees FROM rental, inventory, film WHERE film.film_id = inventory.film_id AND inventory.inventory_id = rental.inventory_id AND rental.rental_date <= p_effective_date AND rental.customer_id = p_customer_id; SELECT COALESCE(SUM(payment.amount),0) INTO v_payments FROM payment WHERE payment.payment_date <= p_effective_date AND payment.customer_id = p_customer_id; RETURN v_rentfees + v_overfees - v_payments; END $function$ |
Exported: 2018-10-12 17:07, Last imported: 2018-08-06 09:25