RevenueComparation8Weeks.sql

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

--declare @ShopID int
--declare @PeriodID int	

--set @ShopID = 6
--set @PeriodID = 1
	
	declare @DateFrom DateTime
	declare @ActualPeriodDateFrom DateTime
	declare @ActualPeriodDateTo DateTime
	declare @Counter int
	declare @DayOfWeek nvarchar(16)
	declare @DateCounter DateTime

	declare @PeriodTable table 
	(
		ID int, 
		ActualPeriodDateFrom DateTime,
		ActualPeriodDateTo DateTime,
		PeriodDescription nvarchar(32)
	)
	
	insert into @PeriodTable
			exec dbo.GetPeriodList_For_RevenueComparation8W

	select top 1
		@ActualPeriodDateFrom = pt.ActualPeriodDateFrom,
		@ActualPeriodDateTo = pt.ActualPeriodDateTo
	
	from @PeriodTable pt
	where pt.ID = @PeriodID

	declare @Matrix table (ID int, DayOfWeek nvarchar(16), week1 DateTime, week2 DateTime, week3 DateTime, week4 DateTime, week5 DateTime, week6 DateTime, week7 DateTime, week8 DateTime)
	
	
	set @Counter = 1
	set @DateCounter = @ActualPeriodDateFrom

	while @Counter <= 7
	begin
		
		while 1 = 1
		begin
			if @Counter = 1
			begin
				set @DayOfWeek = N'Понедельник'	
				break
			end
			
			if @Counter = 2
			begin
				set @DayOfWeek = N'Вторник'	
				break
			end
			
			if @Counter = 3
			begin
				set @DayOfWeek = N'Среда'	
				break
			end
			
			if @Counter = 4
			begin
				set @DayOfWeek = N'Четверг'	
				break
			end
			if @Counter = 5
			begin
				set @DayOfWeek = N'Пятница'	
				break
			end
			
			if @Counter = 6
			begin
				set @DayOfWeek = N'Суббота'	
				break
			end
			
			if @Counter = 7
			begin
				set @DayOfWeek = N'Воскресенье'	
				break
			end
			
			break
		end
		
		insert into @Matrix
			(ID, DayOfWeek, week1, week2, week3, week4, week5, week6, week7, week8)
				values
				(
					@Counter, 
					@DayOfWeek,
					DateAdd(week, -7,@DateCounter),
					DateAdd(week, -6,@DateCounter),
					DateAdd(week, -5,@DateCounter),
					DateAdd(week, -4,@DateCounter),
					DateAdd(week, -3,@DateCounter),
					DateAdd(week, -2,@DateCounter),
					DateAdd(week, -1,@DateCounter),
					@DateCounter
				)
				 
		set @Counter = @Counter + 1
		set @DateCounter = DateAdd(day, 1, @DateCounter)
	end
	
	select top 1
		
		@DateFrom = mx.week1
	
	from @Matrix mx	
	where mx.ID = 1

	declare @TerminalsList table (TerminalID nvarchar(256))

	insert into @TerminalsList
		select
			t.Id as TerminalID
	
		from dbo.Terminals t with (nolock)
		where t.ShopId = @ShopID
	
	
	declare @DaysSales table (WeekDayDate DateTime, Summ decimal(15, 2))

	insert into @DaysSales		
		select
			Cast(ss.CloseTime as Date) as WeekDayDate,
			Sum(Cast(sl.Value/100 as decimal(15, 2))) as Summ

		from @TerminalsList tl 
			inner join dbo.Sessions ss with (nolock) on
					ss.TerminalId = tl.TerminalID
				and ss.CloseTime between @DateFrom and @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
			Cast(ss.CloseTime as Date)

	select
		mx.DayOfWeek,
		isnull(w1_sales.Summ, 0) as w1_Summ,
		isnull(w2_sales.Summ, 0) as w2_Summ,
		isnull(w3_sales.Summ, 0) as w3_Summ,
		isnull(w4_sales.Summ, 0) as w4_Summ,
		isnull(w5_sales.Summ, 0) as w5_Summ,
		isnull(w6_sales.Summ, 0) as w6_Summ,
		isnull(w7_sales.Summ, 0) as w7_Summ,
		isnull(w8_sales.Summ, 0) as w8_Summ

	from @Matrix mx
		left join @DaysSales as w1_sales on w1_sales.WeekDayDate = mx.week1
		left join @DaysSales as w2_sales on w2_sales.WeekDayDate = mx.week2
		left join @DaysSales as w3_sales on w3_sales.WeekDayDate = mx.week3
		left join @DaysSales as w4_sales on w4_sales.WeekDayDate = mx.week4
		left join @DaysSales as w5_sales on w5_sales.WeekDayDate = mx.week5
		left join @DaysSales as w6_sales on w6_sales.WeekDayDate = mx.week6
		left join @DaysSales as w7_sales on w7_sales.WeekDayDate = mx.week7
		left join @DaysSales as w8_sales on w8_sales.WeekDayDate = mx.week8
			
end