Напредни извештаи од базата (SQL и складирани процедури)

Извештај за сите клиенти според месец и година

• Оваа функција вади извештај според месец и година за сите клиенти. Извештајот содржи:

CREATE FUNCTION GetCustomerShipmentsReport(month INT, year INT)
RETURNS TABLE(
    CustomerName varchar(50),
    CustomerTypeName varchar(50),
    TotalWineQuantity bigint,
    TotalWineProfit double precision,
    TotalWineBasePrice double precision,
    TotalWineAgreedPrice double precision,
    PercentageDifference double precision
) 
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
       c.customer_name as CustomerName,
       ct.customer_type_name as CustomerTypeName,
       SUM(wine_quantity) AS TotalWineQuantity,
       SUM(wine_base_price * wine_quantity) AS TotalWineBasePrice,
       SUM(wine_agreed_price * wine_quantity) AS TotalWineAgreedPrice,
       SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) as TotalWineProfit,
       (SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) / SUM(wine_base_price * wine_quantity)) * 100 as PercentageDifference
FROM shipment_load sl
join customer c on c.customer_id = sl.customer_id
join customer_type ct on c.customer_type_id = ct.customer_type_id
JOIN shipment s ON sl.shipment_id = s.shipment_id
WHERE EXTRACT(MONTH FROM s.shipment_date) = month AND EXTRACT(YEAR FROM s.shipment_date) = year
GROUP BY c.customer_name, ct.customer_type_name
order by SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) desc;
END;
$$;

Извештај за сите вина според месец и година