SqlReports

diff --git a/RevenueComparationCCM.sql b/RevenueComparationCCM.sql
new file mode 100644
index 0000000..7cc5dad
--- /dev/null
+++ b/RevenueComparationCCM.sql
@@ -0,0 +1,192 @@
+
+set noexec off
+go
+if object_id('RevenueComparationCCM') is not null
+begin
+	set noexec on
+end	
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.RevenueComparationCCM as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.RevenueComparationCCM
+(
+	 @CompaniesList varchar(max),
+	 @ActualPeriodDateFrom DateTime = null,
+	 @ActualPeriodDateTo DateTime = null,
+	 @LastPeriodDateFrom DateTime = null,
+	 @LastPeriodDateTo DateTime = null
+) 
+as 
+
+begin
+
+--declare @CompaniesList varchar(max)
+--declare @ActualPeriodDateFrom DateTime
+--declare @ActualPeriodDateTo DateTime
+--declare @LastPeriodDateFrom DateTime
+--declare @LastPeriodDateTo DateTime
+
+--Set @CompaniesList = '1'
+--Set @ActualPeriodDateFrom = convert(DateTime, '21.01.2020', 104)
+--Set @ActualPeriodDateTo = convert(DateTime, '21.01.2020', 104)
+--Set @LastPeriodDateFrom = convert(DateTime, '14.01.2020', 104)
+--Set @LastPeriodDateTo = convert(DateTime, '14.01.2020', 104)
+
+	
+	declare @ActualPeriodDateFromInt DateTime
+	declare @ActualPeriodDateToInt DateTime
+	declare @LastPeriodDateFromInt DateTime
+	declare @LastPeriodDateToInt DateTime
+
+	Set @ActualPeriodDateFromInt = cast(@ActualPeriodDateFrom as Date)
+	Set @LastPeriodDateFromInt = cast(@LastPeriodDateFrom as Date)
+	Set @ActualPeriodDateToInt = cast(@ActualPeriodDateTo as Date)
+	Set @LastPeriodDateToInt = cast(@LastPeriodDateTo as Date)
+
+	Set @ActualPeriodDateToInt = dateadd(ms, -2, dateadd(day, 1, @ActualPeriodDateToInt)) 
+	Set @LastPeriodDateToInt = dateadd(ms, -2, dateadd(day, 1, @LastPeriodDateToInt))
+
+	
+	declare @TerminalsList table (TerminalID nvarchar(256), ShopID int)
+
+	insert into @TerminalsList
+		select
+			t.Id as TerminalID,
+			t.ShopID as ShopID
+
+		from dbo.Shops s with (nolock)
+			inner join dbo.Terminals t with (nolock) on
+				t.ShopId = s.Id
+		where
+			s.CompanyId in
+				(
+					select
+						Convert(int, Value)
+					from STRING_SPLIT(@CompaniesList, ',')
+				)
+		and t.HostName <> ''
+		
+	declare @SessionList table (
+			ShopId int,
+			TerminalId nvarchar(256),
+			SessionId bigint,
+			CashCredit decimal (15,2),
+			BankcardCredit decimal (15,2),
+			MobileAppCredit decimal (15,2),
+			LastPeriod tinyint
+
+			INDEX IX_SessionList_ShopId NONCLUSTERED (ShopId),
+			INDEX IX_SessionList_LastPeriod NONCLUSTERED (LastPeriod)
+		)
+
+	insert into @SessionList
+		select
+			tl.ShopID,
+			ss.TerminalId,
+			ss.SessionId,
+			cast(ss.CashCredit as decimal (15,2)) / 100 as CashCredit,
+			cast(ss.BankcardCredit as decimal (15,2)) / 100 as BankcardCredit,
+			cast(ss.MobileAppCredit as decimal (15,2)) / 100 as MobileAppCredit,
+			
+			case 
+				when ss.CloseTime between @LastPeriodDateFromInt and @LastPeriodDateToInt then 1
+				else 0
+			end as LastPeriod 
+			
+		from @TerminalsList tl
+			inner join dbo.Sessions ss with (nolock) on
+					(
+							ss.CloseTime between @ActualPeriodDateFromInt and @ActualPeriodDateToInt
+						or	ss.CloseTime between @LastPeriodDateFromInt and @LastPeriodDateToInt
+					)
+				and ss.TerminalId = tl.TerminalID
+				and (ss.Flags & 2) = 0 -- �������� ��������� ������
+
+	
+		
+	declare @SessionTotals table (
+			ShopId int,
+			CashCreditLastPeriod decimal (15,2),
+			BankcardCreditLastPeriod decimal (15,2),
+			MobileAppCreditLastPeriod decimal (15,2),
+			CashCredit decimal (15,2),
+			BankcardCredit decimal (15,2),
+			MobileAppCredit decimal (15,2)
+
+			INDEX IX_SessionTotals_ShopId NONCLUSTERED (ShopId)
+		)
+
+	insert into @SessionTotals	
+		select
+			t.ShopId,
+			sum(t.CashCreditLastPeriod),
+			sum(t.BankcardCreditLastPeriod),
+			sum(t.MobileAppCreditLastPeriod),
+			sum(t.CashCredit),
+			sum(t.BankcardCredit),
+			sum(t.MobileAppCredit)
+
+		from(
+			select
+				sl.ShopId,
+				0 as CashCreditLastPeriod,
+				0 as BankcardCreditLastPeriod,
+				0 as MobileAppCreditLastPeriod,
+				sum(sl.CashCredit) as CashCredit,
+				sum(sl.BankcardCredit) as BankcardCredit,
+				sum(sl.MobileAppCredit) as MobileAppCredit
+		
+			from @SessionList sl
+			where
+				sl.LastPeriod = 0
+			
+			group by sl.ShopId
+
+			union all
+			
+			select
+				sl.ShopId,
+				sum(sl.CashCredit) as CashCreditLastPeriod,
+				sum(sl.BankcardCredit) as BankcardCreditLastPeriod,
+				sum(sl.MobileAppCredit) as MobileAppCreditLastPeriod,
+				0 as CashCredit,
+				0 as BankcardCredit,
+				0 as MobileAppCredit
+		
+			from @SessionList sl
+			where
+				sl.LastPeriod = 1
+			
+			group by sl.ShopId
+			
+			) as t
+		group by t.ShopId
+		
+
+	select 
+		tl.ShopID as ShopID,
+		s.Name,
+		isnull(SessionTotals.CashCreditLastPeriod, 0) as CashCreditLastPeriod,
+		isnull(SessionTotals.BankcardCreditLastPeriod, 0) as BankcardCreditLastPeriod,
+		isnull(SessionTotals.MobileAppCreditLastPeriod, 0) as MobileAppCreditLastPeriod,				
+		isnull(SessionTotals.CashCredit, 0) as CashCredit,
+		isnull(SessionTotals.BankcardCredit, 0) as BankcardCredit,
+		isnull(SessionTotals.MobileAppCredit, 0) as MobileAppCredit,
+		
+		isnull(SessionTotals.CashCredit, 0) + isnull(SessionTotals.BankcardCredit, 0) + isnull(SessionTotals.MobileAppCredit, 0)
+			- isnull(SessionTotals.CashCreditLastPeriod, 0) - isnull(SessionTotals.BankcardCreditLastPeriod, 0) - isnull(SessionTotals.MobileAppCreditLastPeriod, 0) as Delta
+
+	from @TerminalsList tl
+		inner join dbo.Shops s on s.Id = tl.ShopID
+		left join @SessionTotals SessionTotals on SessionTotals.ShopID = tl.ShopID
+end
+
+go