GetPeriodList_For_RevenueComparation.sql

-- GetPeriodList_For_RevenueComparation

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

begin


--declare @PeriodTypeID Int
--set @PeriodTypeID = 2

	declare @PeriodCount Int
	
	set datefirst 1;
	
	declare @PeriodTable table (
			ID int identity (1, 1), 
			ActualPeriodDateFrom DateTime,
			ActualPeriodDateTo DateTime,
			LastPeriodDateFrom DateTime, 
			LastPeriodDateTo DateTime,
			Period2DateFrom DateTime, 
			Period2DateTo DateTime,
			Period1DateFrom DateTime, 
			Period1DateTo DateTime
		)

	declare @Period1DateFrom as DateTime
	declare @Period1DateTo as DateTime
	declare @Period2DateFrom as DateTime
	declare @Period2DateTo as DateTime
	declare @ActualPeriodDateFrom as DateTime
	declare @ActualPeriodDateTo as DateTime
	declare @LastPeriodDateFrom as DateTime
	declare @LastPeriodDateTo as DateTime

	set @ActualPeriodDateTo = GetDate()

	if (@PeriodTypeID = 1 or @PeriodTypeID = 2)
	begin 
		
		set @PeriodCount = 52

		set @ActualPeriodDateFrom = Cast(
				DateAdd(day, 1 - DatePart(dw, @ActualPeriodDateTo), @ActualPeriodDateTo) as Date
			)
		
		while @PeriodCount > 0
		begin
			set @Period1DateTo = DateAdd(day, -21, @ActualPeriodDateTo)
			set @Period1DateFrom  = DateAdd(day, -21, @ActualPeriodDateFrom)
			set @Period2DateTo = DateAdd(day, -14, @ActualPeriodDateTo)
			set @Period2DateFrom  = DateAdd(day, -14, @ActualPeriodDateFrom)
			set @LastPeriodDateTo = DateAdd(day, -7, @ActualPeriodDateTo)
			set @LastPeriodDateFrom  = DateAdd(day, -7, @ActualPeriodDateFrom)

			insert into @PeriodTable
				(ActualPeriodDateFrom, ActualPeriodDateTo, LastPeriodDateFrom, LastPeriodDateTo, Period2DateFrom, Period2DateTo, Period1DateFrom, Period1DateTo)
					values(@ActualPeriodDateFrom, @ActualPeriodDateTo, @LastPeriodDateFrom, @LastPeriodDateTo, @Period2DateFrom, @Period2DateTo,  @Period1DateFrom, @Period1DateTo)		
			
			set @ActualPeriodDateTo = DateAdd(ms, -2 , @ActualPeriodDateFrom)
			set @ActualPeriodDateFrom = DateAdd(day, -7 , @ActualPeriodDateFrom)
			
			set @PeriodCount = @PeriodCount - 1
		end;

		select 
			pt.ID,
			pt.ActualPeriodDateFrom,
			pt.ActualPeriodDateTo,
			pt.LastPeriodDateFrom,
			pt.LastPeriodDateTo,
			pt.Period2DateFrom,
			pt.Period2DateTo,
			pt.Period1DateFrom,
			pt.Period1DateTo,

			case
				when ID = 1 then N'Текущая неделя'
				when ID = 2 then N'Прошлая неделя'
				else Convert(nvarchar(16), pt.ActualPeriodDateFrom, 104) + ' - ' + Convert(nvarchar(16), pt.ActualPeriodDateTo, 104)
			end as PeiodDescription

		from @PeriodTable pt
		
		order by pt.ID 


	end
	else if (@PeriodTypeID = 3)
	begin
		
		set @PeriodCount = 12

		set @ActualPeriodDateFrom = Cast(
				DateAdd(day, 1 - DatePart(day, @ActualPeriodDateTo), @ActualPeriodDateTo) as Date
			)
		
		while @PeriodCount > 0
		begin
			set @LastPeriodDateTo = DateAdd(month, -1, @ActualPeriodDateTo)
			set @LastPeriodDateFrom  = DateAdd(month, -1, @ActualPeriodDateFrom)

			insert into @PeriodTable
				(ActualPeriodDateFrom, ActualPeriodDateTo, LastPeriodDateFrom, LastPeriodDateTo)
					values(@ActualPeriodDateFrom, @ActualPeriodDateTo, @LastPeriodDateFrom, @LastPeriodDateTo)		
			
			set @ActualPeriodDateTo = DateAdd(ms, -2 , @ActualPeriodDateFrom)
			set @ActualPeriodDateFrom = DateAdd(month, -1 , @ActualPeriodDateFrom)
			
			set @PeriodCount = @PeriodCount - 1
		end;

		select 
			pt.ID,
			pt.ActualPeriodDateFrom,
			pt.ActualPeriodDateTo,
			pt.LastPeriodDateFrom,
			pt.LastPeriodDateTo,
			null as Period2DateFrom,
			null as Period2DateTo,
			null as Period1DateFrom,
			null as Period1DateTo,
			
			case
				when ID = 1 then N'Текущий месяц'
				when ID = 2 then N'Прошлый месяц'
				else Convert(nvarchar(16), pt.ActualPeriodDateFrom, 104) + ' - ' + Convert(nvarchar(16), pt.ActualPeriodDateTo, 104)
			end as PeiodDescription

		from @PeriodTable pt
		
		order by pt.ID 

	end

end

go