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

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

AS
BEGIN
	SET NOCOUNT ON;

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

END
GO