SqlReports
Details
GetPeriodList_For_RevenueComparation.sql 33(+26 -7)
diff --git a/GetPeriodList_For_RevenueComparation.sql b/GetPeriodList_For_RevenueComparation.sql
index 2c3645b..b5b51d7 100644
--- a/GetPeriodList_For_RevenueComparation.sql
+++ b/GetPeriodList_For_RevenueComparation.sql
@@ -38,10 +38,17 @@ begin
ActualPeriodDateFrom DateTime,
ActualPeriodDateTo DateTime,
LastPeriodDateFrom DateTime,
- LastPeriodDateTo DateTime
+ LastPeriodDateTo DateTime,
+ Period2DateFrom DateTime,
+ Period2DateTo DateTime,
+ Period1DateFrom DateTime,
+ Period1DateTo DateTime
)
-
+ declare @Period1DateFrom as DateTime
+ declare @Period1DateTo as DateTime
+ declare @Period2DateFrom as DateTime
+ declare @Period2DateTo as DateTime
declare @ActualPeriodDateFrom as DateTime
declare @ActualPeriodDateTo as DateTime
declare @LastPeriodDateFrom as DateTime
@@ -49,7 +56,7 @@ begin
set @ActualPeriodDateTo = GetDate()
- if (@PeriodTypeID = 1)
+ if (@PeriodTypeID = 1 or @PeriodTypeID = 2)
begin
set @PeriodCount = 52
@@ -60,12 +67,16 @@ begin
while @PeriodCount > 0
begin
+ set @Period1DateTo = DateAdd(day, -21, @ActualPeriodDateTo)
+ set @Period1DateFrom = DateAdd(day, -21, @ActualPeriodDateFrom)
+ set @Period2DateTo = DateAdd(day, -14, @ActualPeriodDateTo)
+ set @Period2DateFrom = DateAdd(day, -14, @ActualPeriodDateFrom)
set @LastPeriodDateTo = DateAdd(day, -7, @ActualPeriodDateTo)
set @LastPeriodDateFrom = DateAdd(day, -7, @ActualPeriodDateFrom)
insert into @PeriodTable
- (ActualPeriodDateFrom, ActualPeriodDateTo, LastPeriodDateFrom, LastPeriodDateTo)
- values(@ActualPeriodDateFrom, @ActualPeriodDateTo, @LastPeriodDateFrom, @LastPeriodDateTo)
+ (ActualPeriodDateFrom, ActualPeriodDateTo, LastPeriodDateFrom, LastPeriodDateTo, Period2DateFrom, Period2DateTo, Period1DateFrom, Period1DateTo)
+ values(@ActualPeriodDateFrom, @ActualPeriodDateTo, @LastPeriodDateFrom, @LastPeriodDateTo, @Period2DateFrom, @Period2DateTo, @Period1DateFrom, @Period1DateTo)
set @ActualPeriodDateTo = DateAdd(ms, -2 , @ActualPeriodDateFrom)
set @ActualPeriodDateFrom = DateAdd(day, -7 , @ActualPeriodDateFrom)
@@ -79,7 +90,11 @@ begin
pt.ActualPeriodDateTo,
pt.LastPeriodDateFrom,
pt.LastPeriodDateTo,
-
+ pt.Period2DateFrom,
+ pt.Period2DateTo,
+ pt.Period1DateFrom,
+ pt.Period1DateTo,
+
case
when ID = 1 then N'������� ������'
when ID = 2 then N'������� ������'
@@ -92,7 +107,7 @@ begin
end
- else if (@PeriodTypeID = 2)
+ else if (@PeriodTypeID = 3)
begin
set @PeriodCount = 12
@@ -122,6 +137,10 @@ begin
pt.ActualPeriodDateTo,
pt.LastPeriodDateFrom,
pt.LastPeriodDateTo,
+ null as Period2DateFrom,
+ null as Period2DateTo,
+ null as Period1DateFrom,
+ null as Period1DateTo,
case
when ID = 1 then N'������� �����'
RevenueComparation.sql 55(+49 -6)
diff --git a/RevenueComparation.sql b/RevenueComparation.sql
index 9ea63f7..008f78b 100644
--- a/RevenueComparation.sql
+++ b/RevenueComparation.sql
@@ -33,15 +33,20 @@ begin
--declare @CompanyID int
--declare @PeriodTypeID int
--declare @PeriodID int
---declare @ActualPeriodDateFrom as DateTime
---declare @ActualPeriodDateTo as DateTime
---declare @LastPeriodDateFrom as DateTime
---declare @LastPeriodDateTo as DateTime
+--declare @ActualPeriodDateFrom DateTime
+--declare @ActualPeriodDateTo DateTime
+--declare @LastPeriodDateFrom DateTime
+--declare @LastPeriodDateTo DateTime
--set @CompanyID = 1
--set @PeriodTypeID = 1
--set @PeriodID = 2
+ declare @Period1DateFrom DateTime
+ declare @Period1DateTo DateTime
+ declare @Period2DateFrom DateTime
+ declare @Period2DateTo DateTime
+
if (
@ActualPeriodDateFrom is null
and @ActualPeriodDateTo is null
@@ -50,12 +55,17 @@ begin
)
begin
+
declare @PeriodTable table (
ID Int,
ActualPeriodDateFrom DateTime,
ActualPeriodDateTo DateTime,
LastPeriodDateFrom DateTime,
LastPeriodDateTo DateTime,
+ Period2DateFrom DateTime,
+ Period2DateTo DateTime,
+ Period1DateFrom DateTime,
+ Period1DateTo DateTime,
PeiodDescription nvarchar(256)
)
@@ -69,7 +79,11 @@ begin
@ActualPeriodDateFrom = pt.ActualPeriodDateFrom,
@ActualPeriodDateTo = pt.ActualPeriodDateTo,
@LastPeriodDateFrom = pt.LastPeriodDateFrom,
- @LastPeriodDateTo = pt.LastPeriodDateTo
+ @LastPeriodDateTo = pt.LastPeriodDateTo,
+ @Period2DateFrom = pt.Period2DateFrom,
+ @Period2DateTo = pt.Period2DateTo,
+ @Period1DateFrom = pt.Period1DateFrom,
+ @Period1DateTo = pt.Period1DateTo
from @PeriodTable pt
where pt.ID = @PeriodID
@@ -116,7 +130,7 @@ begin
and s.CompanyId = @CompanyID
- declare @SalesTotals table (ShopID int, LastPeriodSumm decimal(15, 2), ActualPeriodSumm decimal(15, 2), SalesLastPeriodCount int, SalesActualPeriodCount int)
+ declare @SalesTotals table (ShopID int, Period1Summ decimal(15, 2), Period2Summ decimal(15, 2), LastPeriodSumm decimal(15, 2), ActualPeriodSumm decimal(15, 2), SalesLastPeriodCount int, SalesActualPeriodCount int)
insert into @SalesTotals
select
@@ -125,6 +139,26 @@ begin
Sum
(
case
+ when ss.CloseTime between @Period1DateFrom and @Period1DateTo
+ then Cast(sl.Value/100 as decimal(15, 2))
+
+ else 0
+ end
+ ) as Period1Summ,
+
+ Sum
+ (
+ case
+ when ss.CloseTime between @Period2DateFrom and @Period2DateTo
+ then Cast(sl.Value/100 as decimal(15, 2))
+
+ else 0
+ end
+ ) as Period2Summ,
+
+ Sum
+ (
+ case
when ss.CloseTime between @LastPeriodDateFrom and @LastPeriodDateTo
then Cast(sl.Value/100 as decimal(15, 2))
@@ -169,6 +203,13 @@ begin
and (
ss.CloseTime between @LastPeriodDateFrom and @LastPeriodDateTo
or ss.CloseTime between @ActualPeriodDateFrom and @ActualPeriodDateTo
+ or (
+ @PeriodTypeID = 2
+ and (
+ ss.CloseTime between @Period1DateFrom and @Period1DateTo
+ or ss.CloseTime between @Period2DateFrom and @Period2DateTo
+ )
+ )
)
and (ss.Flags & 2) = 0 -- �������� ��������� ������
@@ -243,6 +284,8 @@ begin
select
gtl.ShopID,
isnull(s.Description, s.Name) as ShopName,
+ isnull(st.Period1Summ, 0) as Period1Summ,
+ isnull(st.Period2Summ, 0) as Period2Summ,
isnull(st.LastPeriodSumm, 0) as LastPeriodSumm,
isnull(st.ActualPeriodSumm, 0) as ActualPeriodSumm,