set noexec off
go
if object_id('GetWeeksSales') is not null
begin
set noexec on
end
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.GetWeeksSales as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
16, -- Severity.
1 -- State.
);
go
set noexec off
go
alter procedure dbo.GetWeeksSales
(
@Date as DateTime,
@CompaniesList varchar(max),
@ExcludedShopsList varchar(max)
)
as
begin
--declare @Date as DateTime
--declare @CompaniesList as varchar(max)
--declare @ExcludedShopsList as varchar(max)
--set @Date = convert(DateTime,'07.09.2020',104)
--set @CompaniesList = '1, 2'
--set @ExcludedShopsList = ''
set DATEFIRST 1
declare @DateFrom as DateTime
declare @DateTo as DateTime
declare @cDate as DateTime
declare @cDateTo as DateTime
declare @CompaniesTable table (CompanyID int index IX_CompaniesTable_CompanyID nonclustered (CompanyID))
insert into @CompaniesTable
select
Convert(int,Value)
from STRING_SPLIT(@CompaniesList, ',')
declare @ExcludedShopsTable table (ShopID int index IX_ExcludedShopsTable_ShopID nonclustered (ShopID))
if isnull(@ExcludedShopsList,'') <> ''
begin
insert into @ExcludedShopsTable
select
Convert(int,Value)
from STRING_SPLIT(@ExcludedShopsList, ',')
end
set @DateTo = dateadd(ms, -2, dateadd(day, 8 - datepart(dw, @Date),@Date))
set @DateFrom = dateadd(ms, 2, dateadd(week, -13, @DateTo))
declare @DateMap table (ID int identity(1,1), Description nvarchar(32), DateFrom DateTime, DateTo DateTime index IX_DateMap_ID nonclustered (ID))
set @cDate = @DateFrom
while @cDate < @DateTo
begin
set @cDateTo = dateadd(ms, -2, dateadd(week, 1, @cDate))
insert into @DateMap
(Description, DateFrom, DateTo)
values(
convert (nvarchar(16), @cDate, 104) + ' - ' + convert (nvarchar(16), @cDateTo, 104),
@cDate,
@cDateTo
)
set @cDate = dateadd(week, 1, @cDate)
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)
inner join dbo.Shops s with (nolock) on s.Id = t.ShopId
inner join @CompaniesTable ct on ct.CompanyID = s.CompanyId
left join @ExcludedShopsTable exst on exst.ShopID = s.Id
where
exst.ShopID is null
select
dm.Description,
t.SalesSumm
from (
select
dm.ID,
Sum(Cast(sl.Value/100 as decimal(15, 2))) as SalesSumm
from @TerminalsList tl
inner join dbo.Sessions ss with (nolock) on
ss.TerminalId = tl.TerminalID
and ss.CloseTime between @DateFrom and @DateTo
and (ss.Flags & 2) = 0 -- Отсекаем отменённые сессии
inner join @DateMap dm on ss.CloseTime between dm.DateFrom and dm.DateTo
inner join dbo.Sales sl with (nolock) on
sl.TerminalId = tl.TerminalID
and sl.SessionId = ss.SessionId
group by
dm.ID
) as t
inner join @DateMap dm on dm.ID = t.ID
order by
t.id
end