set noexec off
go
if object_id('GetAverageCheck') is not null
begin
set noexec on
end
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.GetAverageCheck as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
16, -- Severity.
1 -- State.
);
go
set noexec off
go
alter procedure dbo.GetAverageCheck
(
@CompanyID int,
@DateFrom DateTime,
@DateTo DateTime,
@ShopID int = null
)
as
begin
--declare @CompanyID int
--declare @DateFrom DateTime
--declare @DateTo DateTime
--declare @ShopID int
--set @CompanyID = 1
--set @DateFrom = convert(DateTime, '15.04.2019', 104)
--set @DateTo = convert(DateTime, '21.04.2019', 104)
declare @t1_RowCount int
declare @DateTo_Date DateTime
set @DateTo_Date = cast(@DateTo as Date)
set @DateTo = dateadd(ms, -2, dateadd(day, 1, @DateTo_Date))
declare @t1 table (ShopID int index IX_t1_ShopID, Total decimal(15,2), ItemsCount int, CashCredit int, BankcardCredit int, MobileAppCredit int, ObedRuCredit int, YandexRuCredit int, CardNumber nvarchar(max))
insert into @t1
select
t.ShopId,
cast(sum(sl.Value)/100 as decimal(15,2)) as Total,
sum(sl.Count) as ItemsCount,
ss.CashCredit,
ss.BankcardCredit,
ss.MobileAppCredit,
sc.Value as ObedRuCredit,
scy.Value as YandexRuCredit,
c.CardNumber
from dbo.Shops sh with (nolock)
inner join dbo.Terminals t with (nolock) on sh.Id = t.ShopId
inner join dbo.Sessions ss with (nolock) on
ss.TerminalId = t.Id
and ss.OpenTime between @DateFrom and @DateTo
and (ss.Flags & 2) = 0
inner join dbo.Sales sl with (nolock) on
sl.TerminalId = t.Id
and sl.SessionId = ss.SessionId
left join dbo.Cards c on c.Id = ss.CardId
left join dbo.SessionCredits sc with (nolock) on
sc.TerminalId = t.Id
and sc.SessionId = ss.SessionId
and sc.Type = 5 -- ObedRu
left join dbo.SessionCredits scy with (nolock) on
scy.TerminalId = t.Id
and scy.SessionId = ss.SessionId
and scy.Type = 15 -- Yandex
where
sh.CompanyId = @CompanyID
and ( isnull(@ShopID,0) = 0 or sh.Id = @ShopID )
group by
sl.SessionId,
t.ShopId,
ss.CashCredit,
ss.BankcardCredit,
ss.MobileAppCredit,
sc.Value,
scy.Value,
c.CardNumber
set @t1_RowCount = (select count(*) from @t1)
select
0 as Totals,
isnull(s.Description, s.Name) as ShopName,
t.TotalSumm,
t.AverageCheckSumm,
t.Purchases,
t.Cards,
t.Guests,
t.ByCashCount,
t.ByBankcardCount,
t.ByMobileAppCount,
t.ByObedRuCount,
t.ByYandexRuCount
from (
select
t1.ShopID,
Sum(t1.Total) as TotalSumm,
Cast(Round(Sum(t1.Total) / Sum(1), 2) as decimal (15,2) ) as AverageCheckSumm,
Sum(t1.ItemsCount) as Purchases,
Sum (
case
when t1.CardNumber is null then 0
else 1
end
) as Cards,
Sum (
case
when t1.CardNumber is null then 1
else 0
end
) as Guests,
Sum (
case
when t1.CashCredit > 0 then 1
else 0
end
) as ByCashCount,
Sum (
case
when t1.BankcardCredit > 0 then 1
else 0
end
) as ByBankcardCount,
Sum (
case
when t1.MobileAppCredit > 0 then 1
else 0
end
) as ByMobileAppCount,
Sum (
case
when t1.ObedRuCredit > 0 then 1
else 0
end
) as ByObedRuCount,
Sum (
case
when t1.YandexRuCredit > 0 then 1
else 0
end
) as ByYandexRuCount
from @t1 t1
group by t1.ShopID
) as t
inner join dbo.Shops s on
s.Id = t.ShopID
union all
select
1 as Totals,
null as ShopName,
Sum(t1.Total) as TotalSumm,
Cast(Round(Sum(t1.Total) / @t1_RowCount, 2) as decimal (15,2) ) as AverageCheckSumm,
Sum(t1.ItemsCount) as Purchases,
Sum (
case
when t1.CardNumber is null then 0
else 1
end
) as Cards,
Sum (
case
when t1.CardNumber is null then 1
else 0
end
) as Guests,
Sum (
case
when t1.CashCredit > 0 then 1
else 0
end
) as ByCashCount,
Sum (
case
when t1.BankcardCredit > 0 then 1
else 0
end
) as ByBankcardCount,
Sum (
case
when t1.MobileAppCredit > 0 then 1
else 0
end
) as ByMobileAppCount,
Sum (
case
when t1.ObedRuCredit > 0 then 1
else 0
end
) as ByObedRuCount,
Sum (
case
when t1.YandexRuCredit > 0 then 1
else 0
end
) as ByYandexRuCount
from @t1 t1
end