USE TestSQL_2;

DROP TABLE IF EXISTS dbo.Sales;
DROP TABLE IF EXISTS dbo.Shops;

CREATE TABLE dbo.Shops
(
	Id INT NOT NULL,
	NameShop NVARCHAR(20) NOT NULL,
	CONSTRAINT primary_shops PRIMARY KEY (Id)
);

INSERT INTO dbo.Shops
	(Id, NameShop)
VALUES
	(1, 'ТТ1'), (2, 'ТТ2'), (3, 'ТТ3'), (4, 'ТТ4');

--SELECT * FROM dbo.Shops

CREATE TABLE dbo.Sales
(
	Id INT NOT NULL,
	Date DATETIME NOT NULL,
	Sale DECIMAL(8,2),
	Id_shop INT NOT NULL,
	CONSTRAINT primary_sales PRIMARY KEY (Id),
	CONSTRAINT foreign_sales FOREIGN KEY (Id_shop) REFERENCES Shops (id)
);

INSERT INTO dbo.Sales
	(Id, Date, Sale, Id_shop)
VALUES
	(1, '01.01.2019', 100, 1), (2, '02.01.2020', 100, 1), (3, '03.03.2020', 300, 1), (4, '05.03.2020', 200, 1),
	(5, '01.01.2019', 200, 2), (6, '02.03.2020', 300, 2), (7, '03.03.2020', 300, 2), (8, '08.03.2020', 800, 2),
	(9, '01.01.2019', 100, 3), (10, '02.01.2020', 90, 3), (11, '03.03.2020', 130, 3), (12, '09.03.2020', 200, 3);

--SELECT * FROM dbo.Sales  

DECLARE
	@Month INT = 3,
	@Year INT = 2020

-- ------------------------------------------------------------------------------------------------------------------

--Вариант 1
SELECT	
	Sh.NameShop AS 'Торговая точка',	
	ISNULL(Sa.[Количество продаж], 0) AS 'Количество продаж',	
	FORMAT(Sa.[Поздняя дата], 'dd/MM/yyyy') AS 'Поздняя дата'
FROM
	dbo.Shops AS Sh
LEFT JOIN
	(SELECT
		Id_shop,
		COUNT(Sale) AS 'Количество продаж',
		MAX(Date) AS 'Поздняя дата'
	FROM
		dbo.Sales
	WHERE
		MONTH(Date) = @Month
		AND
		YEAR(Date) = @Year
	GROUP BY
		Id_shop
		) AS Sa
ON
	Sh.Id = Sa.Id_shop;

--Вариант 2
/*SELECT
	NameShop AS 'Торговая точка',
	ISNULL(Sale, 0) AS 'Количество продаж',
	FORMAT(Date, 'dd/MM/yyyy') AS 'Поздняя дата'
FROM
	(SELECT	
		Sh.NameShop,			
		COUNT(Sa.Sale) AS 'Sale',
		MAX(Sa.Date) AS 'Date'
	FROM
		dbo.Shops AS Sh
	LEFT JOIN
		dbo.Sales AS Sa
	ON
		Sh.Id = Sa.Id_shop
	WHERE
		MONTH(Sa.Date) = @Month
		AND
		YEAR(Sa.Date) = @Year
	GROUP BY
		Sh.NameShop) AS Tab;*/

--Вариант 3 (Доработанный Вариант 2)
SELECT
	NameShop AS 'Торговая точка',
	ISNULL(Sale, 0) AS 'Количество продаж',
	FORMAT(Date, 'dd/MM/yyyy') AS 'Поздняя дата'
FROM
	(SELECT	
		Sh.NameShop,			
		COUNT(CASE WHEN MONTH(Sa.Date) = @Month AND YEAR(Sa.Date) = @Year THEN 1 END) AS 'Sale',
		MAX(CASE WHEN MONTH(Sa.Date) = @Month AND YEAR(Sa.Date) = @Year THEN Sa.Date END) AS 'Date'
	FROM
		dbo.Shops AS Sh
	LEFT JOIN
		dbo.Sales AS Sa
	ON
		Sh.Id = Sa.Id_shop		
	GROUP BY
		Sh.NameShop) AS Tab;

--Вариант 4
SELECT	
	Sh.NameShop AS 'Торговая точка',		
	ISNULL(
		(SELECT COUNT(sa.Sale) AS 'Количество продаж' FROM dbo.Sales AS Sa WHERE Sh.Id = Sa.Id_shop AND MONTH(Sa.Date) = @Month AND YEAR(Sa.Date) = @Year),
			0)
		AS 'Количество продаж',
	FORMAT(
		(SELECT MAX(sa.Date) AS 'Поздняя дата' FROM dbo.Sales AS Sa WHERE Sh.Id = Sa.Id_shop AND MONTH(Sa.Date) = @Month AND YEAR(Sa.Date) = @Year ),
		'dd/MM/yyyy')
		AS 'Поздняя дата'
FROM
	dbo.Shops AS Sh;

--Вариант 5
SELECT 
	DISTINCT --!
	Sh.NameShop AS 'Торговая точка',		
	ISNULL(COUNT(Sa.Sale) OVER(PARTITION BY Sh.Id), 0) AS 'Количество продаж',
	FORMAT(MAX(Sa.Date) OVER(PARTITION BY Sh.Id), 'dd/MM/yyyy') AS 'Поздняя дата'	
FROM
	dbo.Shops AS Sh
LEFT JOIN
	dbo.Sales AS Sa
ON
	Sh.Id = Sa.Id_shop
WHERE
	MONTH(Sa.Date) = @Month
	AND
	YEAR(Sa.Date) = @Year;