dbo.GetNetAdSummaryReport.StoredProcedure.sql
7.89 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[GetNetAdSummaryReport] Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNetAdSummaryReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNetAdSummaryReport]
GO
CREATE PROCEDURE [dbo].[GetNetAdSummaryReport] --'2015-05-01','2018-05-01',0,0,0,1,100,1000
-- Add the parameters for the stored procedure here
-- FromDate & ToDate are mandatory
@sFromDate varchar(20), @sToDate varchar(20), @iStartPrice numeric(14,2), @iEndPrice numeric(14,2), @iLicenseTypeId tinyint,
@pageNo int, @pageLength int, @recordCount int out
AS
BEGIN
IF 1=0 BEGIN
SET FMTONLY OFF
END
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
DECLARE @dtFromDate DATETIME
DECLARE @dtToDate DATETIME
DECLARE @cGetSummary CURSOR
DECLARE @iLicenseId INT
DECLARE @iLicenseSubscriptioId INT
DECLARE @iActiveSubscription INT
DECLARE @iRenewSubscription INT
DECLARE @iCancelSubscription INT
DECLARE @iNetAdSubscription INT
DECLARE @sLicenseType VARCHAR(50)
DECLARE @sInstitutionname VARCHAR(100)
DECLARE @dtLicenseCreationDate DATETIME
DECLARE @sAccountType VARCHAR(50)
DECLARE @IsActive BIT
DECLARE @sRenew BIT
-- set the default parameters to 0
SET @iActiveSubscription = 0
SET @iRenewSubscription = 0
SET @iCancelSubscription = 0
-- convert the datatype of fromdate & todate parameter to datetime
SELECT @dtFromDate = CONVERT(DATETIME,@sFromDate)
SELECT @dtToDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sToDate)))
-- create a temporary table to store the first-level of result shown in the netad subscription report on the basis of parameter
CREATE TABLE #NetAdSummaryReport
(
LicenseType VARCHAR(50),
AccountType VARCHAR(50),
InstitutionName VARCHAR(100),
LicenseCreationDate DATETIME,
IsActive BIT,
IsRenew BIT
)
CREATE CLUSTERED INDEX IK_NetAdSummaryReport_1 ON #NetAdSummaryReport (LicenseType, AccountType)
CREATE NONCLUSTERED INDEX IK_NetAdSummaryReport_2 ON #NetAdSummaryReport (IsActive)
-- create a temporary table
CREATE TABLE #NetAdResult
(
LicenseType VARCHAR(50),
AccountType VARCHAR(50),
InstitutionName VARCHAR(100),
LicenseCreationDate DATETIME,
ActiveSubscription INT,
RenewSubscription INT,
InActiveSubscription INT,
NetAdSubscription INT
)
-- define the forward only, read-only cursor
SET @cGetSummary = CURSOR FAST_FORWARD
FOR
SELECT License.Id, LicenseSubscriptionDetail.Id
FROM LicenseSubscriptionDetail
INNER JOIN License ON LicenseSubscriptionDetail.LicenseId = License.Id WHERE
((License.CancellationDate BETWEEN @dtFromDate AND @dtToDate AND License.IsActive = 0 )
OR (License.CreationDate BETWEEN @dtFromDate AND @dtToDate )
OR (RenewalDate BETWEEN @dtFromDate AND @dtToDate))
AND License.LicenseTypeId = (CASE WHEN @iLicenseTypeId > 0 THEN @iLicenseTypeId ELSE License.LicenseTypeId END)
AND (TotalAmount >= (CASE WHEN @iStartPrice > 0 THEN @iStartPrice ELSE 0 END))
AND (TotalAmount <= (CASE WHEN @iEndPrice = 0 THEN 0 WHEN @iEndPrice > 0 THEN @iEndPrice ELSE 9999999999 END))
AND License.LicenseTypeId <> 5
GROUP BY License.Id, LicenseSubscriptionDetail.Id
-- open & fetch the cursor variables into the local variables
OPEN @cGetSummary
FETCH NEXT FROM @cGetSummary INTO @iLicenseId, @iLicenseSubscriptioId
-- start of while loop
WHILE @@FETCH_STATUS = 0
BEGIN
-- fetch the licensetype, accountype & the status of a license
SELECT @sLicenseType = LicenseType.Title, @sAccountType = AccountType.Title,
@sInstitutionname = License.InstitutionName, @dtLicenseCreationDate = License.CreationDate,
@IsActive = License.IsActive,
@sRenew = (CASE WHEN LicenseSubscriptionDetail.RenewalDate IS NULL THEN 0 ELSE 1 END)
FROM License
INNER JOIN LicenseType ON License.LicenseTypeId = LicenseType.Id
INNER JOIN AccountType ON License.AccountTypeId = AccountType.Id
INNER JOIN LicenseSubscriptionDetail ON LicenseSubscriptionDetail.LicenseId = License.Id
WHERE License.Id = @iLicenseId
AND LicenseSubscriptionDetail.Id = @iLicenseSubscriptioId
-- check whether the above query returns any row
IF @@Rowcount > 0
BEGIN
IF @IsActive = 1
BEGIN
IF @sRenew = 1
BEGIN
SET @iRenewSubscription = @iRenewSubscription + 1
END
ELSE
BEGIN
SET @iActiveSubscription = @iActiveSubscription + 1
END
END
ELSE
BEGIN
IF @sRenew = 1
BEGIN
SET @iRenewSubscription = @iRenewSubscription + 1
END
ELSE
BEGIN
SET @iCancelSubscription = @iCancelSubscription + 1
END
END
-- insert into the temporary table
INSERT INTO #NetAdSummaryReport
(LicenseType,AccountType,InstitutionName,LicenseCreationDate,IsActive,IsRenew)
VALUES(@sLicenseType,@sAccountType,@sInstitutionname,@dtLicenseCreationDate,@IsActive,@sRenew)
END
-- fetch the next record from cursor
FETCH NEXT FROM @cGetSummary INTO @iLicenseId, @iLicenseSubscriptioId
-- end of while loop
END
-- close the cursor to free up resources
CLOSE @cGetSummary
DEALLOCATE @cGetSummary
-- Selecting the desired result from temporary table
INSERT INTO #NetAdResult (LicenseType, AccountType,InstitutionName,LicenseCreationDate,ActiveSubscription, RenewSubscription, InActiveSubscription,
NetAdSubscription)
SELECT LicenseType,AccountType,MAX(InstitutionName) as InstitutionName, MAX(LicenseCreationDate) as LicenseCreationDate,(SELECT COUNT(1) FROM #NetAdSummaryReport
WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsActive = 1 AND IsRenew = 0) as ActiveSubscription,
(SELECT COUNT(1) FROM #NetAdSummaryReport
WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsRenew = 1) as RenewSubscription,
(SELECT COUNT(1) FROM #NetAdSummaryReport
WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsActive = 0 AND IsRenew = 0) as InActiveSubscription,
((SELECT COUNT(1) FROM #NetAdSummaryReport
WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsActive = 1 AND IsRenew = 0) + (SELECT COUNT(1) FROM #NetAdSummaryReport
WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsRenew = 1) - (SELECT COUNT(1) FROM #NetAdSummaryReport
WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsActive = 0)) as NetAdSubscription
FROM #NetAdSummaryReport N1 GROUP BY LicenseType,AccountType
-- to show the sum of active, renew, cancel & netad subscriptions
if((Select COUNT(*) from #NetAdResult)>0)
begin
INSERT INTO #NetAdResult (LicenseType,LicenseCreationDate, ActiveSubscription, RenewSubscription, InActiveSubscription,
NetAdSubscription) SELECT 'Total','9999-01-01', @iActiveSubscription, @iRenewSubscription, @iCancelSubscription,
(@iActiveSubscription+@iRenewSubscription-@iCancelSubscription)
End
Select RowNum, LicenseType, AccountType,InstitutionName,CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate, ActiveSubscription, RenewSubscription, InActiveSubscription,
NetAdSubscription
from (
SELECT ROW_NUMBER() OVER (ORDER BY LicenseCreationDate Asc) AS RowNum ,LicenseType, AccountType,InstitutionName,CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate, ActiveSubscription, RenewSubscription, InActiveSubscription,
NetAdSubscription FROM #NetAdResult) as usr
WHERE RowNum > @pageLength * (@pageNo - 1) AND RowNum <= @pageLength * @pageNo order by LicenseCreationDate desc
--Calculate total number of records
select @recordCount = count(ResultTable.NetAdSubscription) from (SELECT LicenseType, AccountType,InstitutionName,CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate, ActiveSubscription, RenewSubscription, InActiveSubscription,
NetAdSubscription FROM #NetAdResult) as ResultTable;
-- Dropping the temporary tables
DROP TABLE #NetAdSummaryReport
DROP TABLE #NetAdResult
END
GO