Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
545 B
1
Indexable
Never
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_calculate_revenue`(
  IN startDate DATE,
  IN endDate DATE
)
BEGIN
  SELECT
    cod.CustomerOrderId,
    co.OrderDate,
    co.Status,
    SUM(cod.Quantity * cod.Price) AS Revenue
  FROM
    (	SELECT 
			CustomerOrderId, OrderDate, Status 
		FROM 
			CustomerOrder 
        WHERE 
			(OrderDate BETWEEN startDate AND endDate) AND (Status = 'Done')) co
    INNER JOIN CustomerOrderDetail cod ON co.CustomerOrderId = cod.CustomerOrderId
  GROUP BY
    co.CustomerOrderId, co.OrderDate, co.Status;
END