Создание представления для выдачи товарооборота по месяцам?

Question

[Rooly]

Есть у меня товар (название, единица измерения)
поставщик (название)
журнал покупок (ид, ид_товара, ид_поставщика, цена, количество (предполагается та же единица измерения, что и товар), дата_покупки (DATE))
журнал продаж (ид, ид_товара, ид_поставщика, цена, количество (предполагается та же единица измерения, что и товар), дата_продажи(DATE))

Мне нужно создать представление (виртуальную таблицу), которая бы мне выводила товарооборот по месяцам (под товароборотом понимается сумма денег (количество * цена), которая была получена в результате продаж за месяц)).

Таблицы

CREATE TABLE products (
idProduct INTEGER NOT NULL ,
title VARCHAR(255) UNIQUE NOT NULL,
measurement_unit VARCHAR(77) NOT NULL ,
PRIMARY KEY(idProduct));

CREATE TABLE providers (
idProvider INTEGER NOT NULL ,
title VARCHAR(77) NOT NULL UNIQUE,
some_address VARCHAR(77),
phone INTEGER NOT NULL,
PRIMARY KEY(idProvider));

CREATE TABLE sales (
idSale INTEGER NOT NULL ,
Product_idProduct INTEGER NOT NULL ,
sale_date DATE DEFAULT GETDATE(),
price INTEGER NOT NULL,
count INTEGER NOT NULL,
PRIMARY KEY(idSale),
FOREIGN KEY(Product_idProduct)
REFERENCES products(idProduct));


CREATE TABLE purchases (
idPurchase INTEGER NOT NULL ,
Product_idProduct INTEGER NOT NULL ,
Provider_idProvider INTEGER NOT NULL ,
count INTEGER NOT NULL,
purchase_date DATE ,
price INTEGER ,
PRIMARY KEY(idPurchase) ,
FOREIGN KEY(Provider_idProvider)
REFERENCES providers(idProvider),
FOREIGN KEY(Product_idProduct)
REFERENCES products(idProduct));

Comments

[Константин Цветков]

Для получения горизонтальных данных по месяцам используйте PIVOT.

[Константин Цветков]

Так продажи или товарооборот (продажи + покупки)?
По контрагентам или по товарам тоже? Или только по товарам?

[Rooly]

Константин Цветков,

Мне хотя бы продажи сделать, товарооборот полностью очень тяжело с моими то знаниями реализовать, я FullStack, но SQL пока только в стадии освоения
Константин Цветков,

Желательно и по контрагентам и по товарам. Правда в данном случае я под контрагентом вижу лишь поставщика, второго лица не вижу.

[Akina]

Так с чем возникла проблема? С правильным связыванием таблиц? с агрегацией? с трансформацией в сводную?

[Rooly]

Akina,
Со всем вместе взятым.
Такое я еще не делал, поэтому хочу научиться.
Если напишите, могу предложить небольшое вознаграждение

[Akina]

Rooly, вот чему можно научиться, если кто-то всё сделает за тебя, а?

Давай так. Вот fiddle с твоими структурами (слегка поправлено) и примером вставки данных. Корректируешь, вставляешь свой пример данных (без фанатизма там, не надо полтыщи записей...). Публикуешь обновлённую ссылку (вставить в текст вопроса), и добавляешь требуемый ответ для именно этих данных (в виде таблицы) с подробными пояснениями. На этом материале уже можно будет учиться...

[Константин Цветков]

Akina, Поскольку это тестовые данные можно:

INSERT INTO sales (Product_idProduct, sale_date, price, [count])
  SELECT TOP(100) (RAND(id) * 3) + 1, 
                   DATEADD(dd, - (1000 * RAND(id)), GETDATE()), 
                   (RAND(id) * 100) + 1, (RAND(id) * 10) + 1
    FROM sysobjects
    ORDER BY id DESC

Answer 1

[Константин Цветков]

В соседнем вопросе я уже давал ссылку на календарный отчёт (простой). Используйте его как шаблон. И не забывайте, что PIVOT, это в некотором роде группировка. Сделайте запрос по sales и месяцам. SUM([price] * [count]) будет в ячейках, код продукта по вертикали. Потом добавите во внутренний запрос INNER JOIN к таблице products и вместо кода товара можете использовать его наименование.
Про товарооборот продумайте порядок: поставщик - товар или наоборот и нужно ли суммировать поступление и продажу или в месяце сделать две колонки. И ещё: год - сделать страницы по годам или ещё как?

Например, так:

SELECT [Year], [Product], [Январь], [Февраль], [Март], 
                          [Апрель], [Май], [Июнь], 
                          [Июль], [Август], [Сентябрь], 
                          [Октябрь], [Ноябрь], [Декабрь]
    FROM 
      ( SELECT YEAR(sale_date) AS [Year],
               products.title AS [Product], 
               DATENAME(MONTH, sale_date) AS [MonthName],
               price * count AS SaleSum
          FROM sales 
            INNER JOIN products ON sales.Product_idProduct = products.idProduct) AS enMonth
    PIVOT ( SUM(SaleSum)  
     FOR [MonthName] IN ( [Январь], [Февраль], [Март], 
                          [Апрель], [Май], [Июнь], 
                          [Июль], [Август], [Сентябрь], 
                          [Октябрь], [Ноябрь], [Декабрь])
          ) AS enPivot
  ORDER BY 1 DESC, 2

Русские названия месяцев:

SET LANGUAGE Russian;  
SELECT DATENAME(MONTH, 1), DATENAME(MONTH, 32), DATENAME(MONTH, 60)

Январь Февраль Март