RevenueComparationInsideTheDay.sql

set noexec off
go
if object_id('RevenueComparationInsideTheDay') is not null
begin
	set noexec on
end	
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.RevenueComparationInsideTheDay as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
				16, -- Severity.
				1 -- State.
			);
go
set noexec off
go
alter procedure dbo.RevenueComparationInsideTheDay
(
	@CompanyID int,
	@ShopID int,
	@Date1 DateTime,
	@Date2 DateTime
) 
as 
	
begin

--declare @CompanyID int
--declare @ShopID int
--declare @Date1 DateTime
--declare @Date2 DateTime

--set @CompanyID = 1
--set @ShopID = 6
--set @Date1 = '20190527'
--set @Date2 = '20190526'
		
	declare @ActualPeriodDateFrom DateTime
	declare @ActualPeriodDateTo DateTime
	declare @LastPeriodDateFrom DateTime
	declare @LastPeriodDateTo DateTime
	
	declare @PeriodTable table (
			ActualPeriodDateFrom DateTime,
			ActualPeriodDateTo DateTime,
			LastPeriodDateFrom DateTime, 
			LastPeriodDateTo DateTime
		)
	
	insert into @PeriodTable
		exec dbo.GetPeriods_For_RevenueComparationDaily @Date1, @Date2 
	
	select top 1
		@ActualPeriodDateFrom = pt.ActualPeriodDateFrom,
		@ActualPeriodDateTo = pt.ActualPeriodDateTo,
		@LastPeriodDateFrom = pt.LastPeriodDateFrom,
		@LastPeriodDateTo = pt.LastPeriodDateTo

	from @PeriodTable pt


	declare @CurDate1 datetime
	declare @CurDate2 datetime
	declare @CurDate1To datetime
	declare @CurDate2To datetime
	declare @PeriodDescr varchar(32)
	
	set @CurDate1 = @ActualPeriodDateFrom
	set @CurDate2 = @LastPeriodDateFrom

	declare @DateMatrix table (ID int identity(1,1), PeriodDescr varchar(32), ActualPeriodDateFrom DateTime, ActualPeriodDateTo DateTime, LastPeriodDateFrom DateTime, LastPeriodDateTo DateTime)

	while (@CurDate1 < @ActualPeriodDateTo) 
	begin
		
		set @CurDate1To = dateadd(ms, -2, dateadd(hour, 1, @CurDate1))
		set @CurDate2To = dateadd(ms, -2, dateadd(hour, 1, @CurDate2))
		
		if (@CurDate1To > @ActualPeriodDateTo) set @CurDate1To = @ActualPeriodDateTo
		if (@CurDate2To > @LastPeriodDateTo) set @CurDate2To = @LastPeriodDateTo

		set @PeriodDescr = substring(convert(varchar(32),@CurDate1, 120), 12, 5) + ' - ' + substring(convert(varchar(32),@CurDate1To, 120), 12, 8)

		insert into @DateMatrix
			(PeriodDescr, ActualPeriodDateFrom, ActualPeriodDateTo, LastPeriodDateFrom, LastPeriodDateTo)
				values(@PeriodDescr, @CurDate1, @CurDate1To, @CurDate2, @CurDate2To)
		
		set @CurDate1 = dateadd(hour, 1, @CurDate1)
		set @CurDate2 = dateadd(hour, 1, @CurDate2)
	end
	
	declare @TerminalsList table (TerminalID nvarchar(256), ShopID int)

	insert into @TerminalsList
		select
			t.Id as TerminalID,
			t.ShopID as ShopID

		from dbo.Terminals t with (nolock)
		where
				t.ShopID = @ShopID

	declare @SalesTotals table (PeriodID int, LastPeriodSumm decimal(15, 2), ActualPeriodSumm decimal(15, 2), SalesLastPeriodCount int, SalesActualPeriodCount int)

	insert into @SalesTotals
		select
			dm.ID as PeriodID,
			
			Sum
			(
				case
					when ss.CloseTime between dm.LastPeriodDateFrom and dm.LastPeriodDateTo
						then Cast(sl.Value/100 as decimal(15, 2))

					else 0
				end
			) as LastPeriodSumm,
			
			Sum
			(
				case
					when ss.CloseTime between dm.ActualPeriodDateFrom and dm.ActualPeriodDateTo
						then Cast(sl.Value/100 as decimal(15, 2))
					
					else 0
				end
			) as ActualPeriodSumm,


			Sum
			(
				case
					when ss.CloseTime between dm.LastPeriodDateFrom and dm.LastPeriodDateTo
						then sl.Count

					else 0
				end
			) as SalesLastPeriodCount,
			
			Sum
			(
				case
					when ss.CloseTime between dm.ActualPeriodDateFrom and dm.ActualPeriodDateTo
						then sl.Count
					
					else 0
				end
			) as SalesActualPeriodCount

		from @TerminalsList tl
			inner join @DateMatrix dm on 1 = 1
			inner join dbo.Sessions ss with (nolock) on
					ss.TerminalId = tl.TerminalID
				and	(
							ss.CloseTime between dm.LastPeriodDateFrom and dm.LastPeriodDateTo 
						or	ss.CloseTime between dm.ActualPeriodDateFrom and dm.ActualPeriodDateTo
					)
				and (ss.Flags & 2) = 0 -- Отсекаем отменённые сессии
			
			inner join dbo.Sales sl with (nolock) on
					sl.TerminalId = tl.TerminalID
				and sl.SessionId = ss.SessionId

		group by dm.ID
	
	select
		dm.PeriodDescr as PeriodDescr,
		isnull(st.LastPeriodSumm, 0) as LastPeriodSumm,
		isnull(st.ActualPeriodSumm, 0) as ActualPeriodSumm,

		isnull(st.ActualPeriodSumm, 0) - isnull(st.LastPeriodSumm, 0) as SummDifference,

		case 
			when isnull(st.LastPeriodSumm, 0) <> 0 
				then
					Cast(isnull(st.ActualPeriodSumm, 0)/isnull(st.LastPeriodSumm, 0) * 100  - 100 as decimal (15, 2))
			
			else 0
		end as SummDifferenceRate
	
	from @SalesTotals st
		inner join @DateMatrix dm on dm.ID = st.PeriodID 
	
	order by dm.ID		
			
			
end