Creation of representation for delivery of goods turnover on months?

Question

SQL Server

I have a product (name, unit of measure)
supplier (name)
purchase log (id, product_id, supplier_id, price, quantity (assuming the same unit of measure as the product), purchase_date (DATE))
sales log (id, product_id, supplier_id, price, quantity (assuming the same unit of measure as the product), sale_date(DATE)

) which was received as a result of sales for the month)).

tables

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));

Answer 1

In the next question, I already gave a link to the calendar report (simple). Use it as a template. And don't forget that PIVOT, is in some way a grouping. Make a request by salesmonth. SUM([price] * [count])will be in cells, product code vertically. Then add to the internal query INNER JOINto the table productsand instead of the product code, you can use its name.
Regarding the turnover, think over the order: the supplier is the goods or vice versa, and whether it is necessary to sum up the receipt and sale or make two columns in a month. And one more thing: year - make pages by year or something else?

For example, like this:

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)

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