set noexec off
go
if object_id('GetSalesByOZON') is not null
begin
set noexec on
end
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.GetSalesByOZON as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
16, -- Severity.
1 -- State.
);
go
set noexec off
go
alter procedure dbo.GetSalesByOZON
(
@DateFrom as DateTime,
@DateTo as DateTime,
@ShopsList as varchar(max) = null
)
as
begin
--declare @DateFrom as DateTime
--declare @DateTo as DateTime
--declare @ShopsList as varchar(max)
--set @DateFrom = Convert(DateTime,'10.09.2020', 104)
--set @DateTo= Convert(DateTime,'10.09.2020', 104)
--set @ShopsList = '1,4'
declare @ShopsTable table (ID int)
insert into @ShopsTable
select
Convert(int, Value)
from STRING_SPLIT(@ShopsList, ',')
declare @DateFromInt as DateTime
declare @DateToInt as DateTime
set @DateFromInt = cast(@DateFrom as Date)
set @DateToInt = cast(@DateTo as Date)
set @DateToInt = dateadd(ms, -2, dateadd(day, 1, @DateToInt))
select
erSalesStats.HostName,
p.Name,
erSalesStats.Count,
erSalesStats.Summ
from (
select
t.HostName,
s.ProductId,
Sum(s.Count) as Count,
Sum(cast(cast(s.Value as decimal(15,2))/100 as decimal(15,2))) as Summ
from @ShopsTable st
inner join dbo.Terminals t with (nolock)on st.ID = t.ShopId
inner join dbo.Sessions ss with (nolock) on
ss.CloseTime between @DateFromInt and @DateToInt
and ss.TerminalId = t.Id
inner join dbo.SessionCredits sc with (nolock) on
sc.SessionId = ss.SessionId
and sc.TerminalId = t.Id
and sc.Type = 8
inner join dbo.Sales s with (nolock) on
s.SessionId = ss.SessionId
and s.TerminalId = t.Id
group by
t.HostName,
s.ProductId
) as erSalesStats
left join dbo.Products p on p.Id = erSalesStats.ProductId
order by
erSalesStats.HostName
end