set noexec off
go
if object_id('TotalRevenueByCategories') is not null
begin
set noexec on
end
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.TotalRevenueByCategories as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
16, -- Severity.
1 -- State.
);
go
set noexec off
go
alter procedure dbo.TotalRevenueByCategories
(
@PeriodTypeID int,
@PeriodID int
)
as
begin
--declare @PeriodTypeID int
--declare @PeriodID int
--set @PeriodTypeID = 1
--set @PeriodID = 2
declare @DateFrom datetime
declare @DateTo datetime
declare @PeriodTable table (
ID Int,
ActualPeriodDateFrom DateTime,
ActualPeriodDateTo DateTime,
LastPeriodDateFrom DateTime,
LastPeriodDateTo DateTime,
Period2DateFrom DateTime,
Period2DateTo DateTime,
Period1DateFrom DateTime,
Period1DateTo DateTime,
PeiodDescription nvarchar(256)
)
insert into @PeriodTable
exec dbo.GetPeriodList_For_RevenueComparation @PeriodTypeID
select
@DateFrom = pt.ActualPeriodDateFrom,
@DateTo = pt.ActualPeriodDateTo
from @PeriodTable pt
where
pt.ID = @PeriodID
declare @TerminalsList table (TerminalID nvarchar(256), ShopID int, CompanyID int)
insert into @TerminalsList
select
t.Id as TerminalID,
t.ShopID as ShopID,
s.CompanyID as CompanyID
from dbo.Terminals t with (nolock)
inner join dbo.Shops s with (nolock) on
s.Id = t.ShopId
and isnull(s.CompanyId, 0) <> 0
declare @Sales table (
CompanyID int,
ShopID int,
CategoryID int,
SalesSumm decimal(15, 2),
SalesCount int
INDEX IX_Sales_CompanyID NONCLUSTERED (CompanyID)
)
insert into @Sales
select
tl.CompanyID,
tl.ShopID,
p.CategoryId,
Sum(Cast(sl.Value/100 as decimal(15, 2))) as SalesSumm,
Sum(sl.Count) as SalesCount
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 dbo.Sales sl with (nolock) on
sl.TerminalId = tl.TerminalID
and sl.SessionId = ss.SessionId
left join dbo.Products p with (nolock) on p.Id = sl.ProductId
group by
tl.CompanyID,
tl.ShopID,
p.CategoryID
declare @ShopsCount table (
CompanyID int,
ShopsCount int,
CoffeeShopsCount int
INDEX IX_ShopsCount_CompanyID NONCLUSTERED (CompanyID)
)
insert into @ShopsCount
select
t.CompanyID,
Sum(1) as ShopsCount,
Sum (
case
when (isnull(pg.Flags, 0) & 1) <> 0 then 1
else 0
end
) as CoffeeShopsCount
from (
select
sl.CompanyID,
sl.ShopID
from @Sales sl
group by
sl.CompanyID,
sl.ShopID
) as t
inner join dbo.Shops s with (nolock) on s.Id = t.ShopID
left join dbo.Planograms pg with (nolock) on pg.Id = s.PlanogramId
group by
t.CompanyID
declare @SalesTotals table (
CompanyID int,
CategoryID int,
SalesSumm decimal(15, 2),
SalesCount int
INDEX IX_SalesTotals_CompanyID NONCLUSTERED (CompanyID),
INDEX IX_SalesTotals_CategoryID NONCLUSTERED (CategoryID)
)
insert into @SalesTotals
select
sl.CompanyID,
isnull(sl.CategoryID, 0) as CategoryID,
Sum(sl.SalesSumm) as SalesSumm,
Sum(sl.SalesCount) as SalesCount
from @Sales sl
group by
sl.CompanyID,
sl.CategoryID
declare @SalesSummByCategories table (
CompanyID int,
CategorySumm01 decimal (15,2),
CategorySumm02 decimal (15,2),
CategorySumm03 decimal (15,2),
CategorySumm04 decimal (15,2),
CategorySumm05 decimal (15,2),
CategorySumm06 decimal (15,2),
CategorySumm07 decimal (15,2),
CategorySumm08 decimal (15,2),
CategorySumm09 decimal (15,2),
SummWithoutCategory decimal (15,2)
INDEX IX_SalesSummByCategories_CompanyID NONCLUSTERED (CompanyID)
)
insert into @SalesSummByCategories
select
CompanyID,
isnull([1], 0) as CategorySumm01,
isnull([2], 0) as CategorySumm02,
isnull([3], 0) as CategorySumm03,
isnull([4], 0) as CategorySumm04,
isnull([5], 0) as CategorySumm05,
isnull([6], 0) as CategorySumm06,
isnull([7], 0) as CategorySumm07,
isnull([8], 0) as CategorySumm08,
isnull([9], 0) as CategorySumm09,
isnull([0], 0) as SummWithoutCategory
from (
select
CompanyID,
CategoryID,
SalesSumm
from @SalesTotals
) as SorceTable
pivot
(
Sum(SalesSumm)
for CategoryID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [0])
) as PivotTable
declare @SalesCountByCategories table (
CompanyID int,
CategoryCount01 int,
CategoryCount02 int,
CategoryCount03 int,
CategoryCount04 int,
CategoryCount05 int,
CategoryCount06 int,
CategoryCount07 int,
CategoryCount08 int,
CategoryCount09 int,
CountWithoutCategory int
INDEX IX_SalesSummByCategories_CompanyID NONCLUSTERED (CompanyID)
)
insert into @SalesCountByCategories
select
CompanyID,
isnull([1], 0) as CategoryCount01,
isnull([2], 0) as CategoryCount02,
isnull([3], 0) as CategoryCount03,
isnull([4], 0) as CategoryCount04,
isnull([5], 0) as CategoryCount05,
isnull([6], 0) as CategoryCount06,
isnull([7], 0) as CategoryCount07,
isnull([8], 0) as CategoryCount08,
isnull([9], 0) as CategoryCount09,
isnull([0], 0) as SummWithoutCategory
from (
select
CompanyID,
CategoryID,
SalesCount
from @SalesTotals
) as SorceTable
pivot
(
Sum(SalesCount)
for CategoryID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [0])
) as PivotTable
select
datepart(year, @DateFrom) as Year,
datepart(week, @DateFrom) as Week,
@DateFrom as DateFrom,
@DateTo as DateTo,
c.Name,
isnull(sc.ShopsCount, 0 ) as ShopsCount,
isnull(sc.CoffeeShopsCount, 0) as CoffeeShopsCount,
isnull(totals.SalesCount, 0) as SalesCount,
isnull(scbc.CategoryCount01, 0) as CategoryCount01,
isnull(scbc.CategoryCount02, 0) as CategoryCount02,
isnull(scbc.CategoryCount03, 0) as CategoryCount03,
isnull(scbc.CategoryCount04, 0) as CategoryCount04,
isnull(scbc.CategoryCount05, 0) as CategoryCount05,
isnull(scbc.CategoryCount06, 0) as CategoryCount06,
isnull(scbc.CategoryCount07, 0) as CategoryCount07,
isnull(scbc.CategoryCount08, 0) as CategoryCount08,
isnull(scbc.CategoryCount09, 0) as CategoryCount09,
isnull(scbc.CountWithoutCategory, 0) as CountWithoutCategory,
isnull(totals.SalesSumm, 0) as SalesSumm,
isnull(ssbc.CategorySumm01, 0) as CategorySumm01,
isnull(ssbc.CategorySumm02, 0) as CategorySumm02,
isnull(ssbc.CategorySumm03, 0) as CategorySumm03,
isnull(ssbc.CategorySumm04, 0) as CategorySumm04,
isnull(ssbc.CategorySumm05, 0) as CategorySumm05,
isnull(ssbc.CategorySumm06, 0) as CategorySumm06,
isnull(ssbc.CategorySumm07, 0) as CategorySumm07,
isnull(ssbc.CategorySumm08, 0) as CategorySumm08,
isnull(ssbc.CategorySumm09, 0) as CategorySumm09,
isnull(ssbc.SummWithoutCategory, 0) as SummWithoutCategory
from dbo.Companies c with (nolock)
left join @ShopsCount as sc on sc.CompanyID = c.Id
left join @SalesCountByCategories scbc on scbc.CompanyID = c.Id
left join @SalesSummByCategories ssbc on ssbc.CompanyID = c.Id
left join (
select
st.CompanyID,
Sum(st.SalesSumm) as SalesSumm,
Sum(st.SalesCount) as SalesCount
from @SalesTotals as st
group by
st.CompanyID
) as totals on totals.CompanyID = c.Id
end