GetPeriodList_For_RevenueComparation8W.sql

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

begin
	
	set datefirst 1;

	declare @PeriodTable table 
	(
		ID int identity (1, 1), 
		ActualPeriodDateFrom DateTime,
		ActualPeriodDateTo DateTime
	)
	
	declare @PeriodCount as Int
	declare @ActualPeriodDateFrom as DateTime
	declare @ActualPeriodDateTo as DateTime
	
	set @ActualPeriodDateTo = GetDate()
	set @PeriodCount = 52

	set @ActualPeriodDateFrom = Cast(
			DateAdd(day, 1 - DatePart(dw, @ActualPeriodDateTo), @ActualPeriodDateTo) as Date
		)
	
	set @ActualPeriodDateTo = DateAdd(ms, -2, DateAdd(day, 7, @ActualPeriodDateFrom))
	
	while @PeriodCount > 0
	begin
	
		insert into @PeriodTable
			(ActualPeriodDateFrom, ActualPeriodDateTo)
				values(@ActualPeriodDateFrom, @ActualPeriodDateTo)		
		
		set @ActualPeriodDateTo = DateAdd(ms, -2 , @ActualPeriodDateFrom)
		set @ActualPeriodDateFrom = DateAdd(day, -7 , @ActualPeriodDateFrom)
		
		set @PeriodCount = @PeriodCount - 1
	end

	select 
		pt.ID,
		pt.ActualPeriodDateFrom,
		pt.ActualPeriodDateTo,

		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