SqlReports
Details
GetSalesByOZON.sql 81(+81 -0)
diff --git a/GetSalesByOZON.sql b/GetSalesByOZON.sql
new file mode 100644
index 0000000..777ff85
--- /dev/null
+++ b/GetSalesByOZON.sql
@@ -0,0 +1,81 @@
+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
+)
+as
+begin
+
+--declare @DateFrom as DateTime
+--declare @DateTo as DateTime
+--set @DateFrom = Convert(DateTime,'10.09.2020', 104)
+--set @DateTo= Convert(DateTime,'10.09.2020', 104)
+
+ 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 dbo.Terminals t with (nolock)
+ 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
+
+
+ where
+ t.HostName like '%OZON%'
+
+ group by
+ t.HostName,
+ s.ProductId
+
+ ) as erSalesStats
+
+ left join dbo.Products p on p.Id = erSalesStats.ProductId
+
+ order by
+ erSalesStats.HostName
+
+end
\ No newline at end of file
GetTotalSalesByOZON.sql 76(+76 -0)
diff --git a/GetTotalSalesByOZON.sql b/GetTotalSalesByOZON.sql
new file mode 100644
index 0000000..0c454e4
--- /dev/null
+++ b/GetTotalSalesByOZON.sql
@@ -0,0 +1,76 @@
+set noexec off
+go
+if object_id('GetTotalSalesByOZON') is not null
+begin
+ set noexec on
+end
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.GetTotalSalesByOZON as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+ 16, -- Severity.
+ 1 -- State.
+ );
+go
+set noexec off
+go
+alter procedure dbo.GetTotalSalesByOZON
+(
+ @DateFrom as DateTime,
+ @DateTo as DateTime
+)
+as
+begin
+
+--declare @DateFrom as DateTime
+--declare @DateTo as DateTime
+--set @DateFrom = Convert(DateTime,'10.09.2020', 104)
+--set @DateTo= Convert(DateTime,'10.09.2020', 104)
+
+ 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
+ p.Name,
+ erSalesStats.Count,
+ erSalesStats.Summ
+
+ from (
+ select
+ s.ProductId,
+ Sum(s.Count) as Count,
+ Sum(cast(cast(s.Value as decimal(15,2))/100 as decimal(15,2))) as Summ
+
+
+ from dbo.Terminals t with (nolock)
+ 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
+
+
+ where
+ t.HostName like '%OZON%'
+
+ group by
+ s.ProductId
+
+ ) as erSalesStats
+
+ left join dbo.Products p on p.Id = erSalesStats.ProductId
+
+
+end
\ No newline at end of file