KF0KfUPS

1DROP TABLE IF EXISTS dish_cost;
2
3CREATE TEMPORARY TABLE IF NOT EXISTS dish_cost AS 
4SELECT DISH.Code_D, DISH.Nam_D,
5  (
6     SELECT SUM(INGRED.Qt_P * PROD.Price_P) 
7       FROM INGRED
8            INNER JOIN
9            PROD ON INGRED.Code_P = PROD.Code_P
10      WHERE DISH.Code_D = INGRED.Code_D
11    ) AS cost
12FROM DISH;
13
14
15SELECT Date,
16       YIELD.Qt_Y AS "Продано",
17       DISH.Nam_D AS "Блюдо",
18       ((((DISH.Cost_D + (SELECT cost FROM dish_cost WHERE DISH.Code_D = dish_cost.Code_D)) / 100 * (100 + DISH.Incr)) * YIELD.Qt_Y)) AS "Выручка"
19       
20FROM YIELD
21       INNER JOIN
22       DISH ON DISH.Code_D = YIELD.Code_D
23
24WHERE Date >= Date('2020-11-01') AND 
25       Date < Date('2020-12-01', '+1 day') 
26       
27ORDER BY YIELD.Date
28