Хранимая процедура для вычисление валовой прибыли из таблицы покупки и продажи


USE [ESEP]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.esep_ReportSalesMorjaByDate', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.esep_ReportSalesMorjaByDate;
GO

CREATE PROCEDURE [dbo].[esep_ReportSalesMorjaByDate]
@DateStart datetime,
@DateEnd datetime

AS
BEGIN
SET NOCOUNT ON;

SELECT ps.DocumentNum, MAX(ps.DateTime) as 'DateTime', SUM(ps.Quantity * ps.Price) as 'SaleSum',
SUM(ps.Quantity * (SELECT TOP 1 pc.Price
FROM tbl_ProductsComing pc
WHERE pc.ProductsID = ps.ProductsID and pc.DateTime <= ps.DateTime
ORDER BY pc.DateTime DESC)) as 'ComeSum',
c.Name as 'CustomerName', u.Name as 'UserName'
FROM tbl_ProductsSelling ps INNER JOIN tbl_Customers c ON c.ID = ps.CustomersID
INNER JOIN tbl_Users u ON u.ID = ps.UsersID
WHERE ps.DateTime >= @DateStart and ps.DateTime <= @DateEnd
GROUP BY ps.DocumentNum, c.Name, u.Name

END
GO