-- 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
)
declare @ActualPeriodDateFrom as DateTime
declare @ActualPeriodDateTo as DateTime
declare @LastPeriodDateFrom as DateTime
declare @LastPeriodDateTo as DateTime
set @ActualPeriodDateTo = GetDate()
if (@PeriodTypeID = 1)
begin
set @PeriodCount = 52
set @ActualPeriodDateFrom = Cast(
DateAdd(day, 1 - DatePart(dw, @ActualPeriodDateTo), @ActualPeriodDateTo) as Date
)
while @PeriodCount > 0
begin
set @LastPeriodDateTo = DateAdd(day, -7, @ActualPeriodDateTo)
set @LastPeriodDateFrom = DateAdd(day, -7, @ActualPeriodDateFrom)
insert into @PeriodTable
(ActualPeriodDateFrom, ActualPeriodDateTo, LastPeriodDateFrom, LastPeriodDateTo)
values(@ActualPeriodDateFrom, @ActualPeriodDateTo, @LastPeriodDateFrom, @LastPeriodDateTo)
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,
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 = 2)
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,
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