dbo.GetNetAdDetailReport.StoredProcedure.sql
8.63 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
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[GetNetAdDetailReport] Script Date: 02/06/2018 10:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetNetAdDetailReport]
-- Add the parameters for the stored procedure here
-- FromDate, ToDate, LicenseType & AccountType are mandatory
@sFromDate varchar(20), @sToDate varchar(20), @sLicenseType varchar(50), @sAccountType varchar(50),
@iStartPrice numeric(14,2), @iEndPrice numeric(14,2)
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 @cGetLicenseId CURSOR
DECLARE @sAccountNumber VARCHAR(50)
DECLARE @sLicenseeName VARCHAR(100)
DECLARE @iLicenseId INT
DECLARE @iLicenseSubscriptionDetailId INT
DECLARE @sEdition VARCHAR(200)
DECLARE @dtRenewalDate DATETIME
DECLARE @LicenseStatus VARCHAR(10)
-- 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 second-level after clicking on the first-level of result shown in the netad subscription report on the basis of parameter
CREATE TABLE #NetAdDetailReport
(
AccountNumber VARCHAR(50),
LicenseeName VARCHAR(100),
LicenseType VARCHAR(50),
Edition VARCHAR(200),
RenewalDate DATETIME
)
-- define the forward only, read-only cursor
SET @cGetLicenseId = 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 (SubscriptionValidFrom BETWEEN @dtFromDate AND @dtToDate ))
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))
GROUP BY License.Id, LicenseSubscriptionDetail.Id
-- open & fetch the cursor variables into the local variables
OPEN @cGetLicenseId
FETCH NEXT FROM @cGetLicenseId INTO @iLicenseId, @iLicenseSubscriptionDetailId
-- start of while loop
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sEdition = ''
-- fetch the accountnumber, licenseename of a license
SELECT @sAccountNumber = AccountNumber, @sLicenseeName = (LicenseeFirstName+' '+LicenseeLastName)
FROM License
INNER JOIN LicenseType ON License.LicenseTypeId = LicenseType.Id
INNER JOIN AccountType ON License.AccountTypeId = AccountType.Id
WHERE License.Id = @iLicenseId
AND License.IsActive = 1
AND LicenseType.Title = @sLicenseType
AND AccountType.Title = @sAccountType
-- check whether the above query returns any row
IF @@Rowcount > 0
BEGIN
-- fetch the renewal date of the license
SELECT @dtRenewalDate = LicenseSubscriptionDetail.RenewalDate
FROM LicenseSubscriptionDetail
WHERE LicenseSubscriptionDetail.Id = @iLicenseSubscriptionDetailId
-- fetch all the editions mapped as a string with a license
SELECT @sEdition = Edition.Title + '; ' + @sEdition
FROM LicenseToEdition INNER JOIN Edition
ON LicenseToEdition.EditionId = Edition.Id
WHERE LicenseToEdition.LicenseId = @iLicenseId
-- remove the trailing comma-separator from the edition-string
SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1);
-- insert into the temporary table
INSERT INTO #NetAdDetailReport
(AccountNumber,LicenseeName,LicenseType,Edition,RenewalDate)
VALUES(@sAccountNumber,@sLicenseeName,@sLicenseType,@sEdition,@dtRenewalDate)
END
-- fetch the next record from cursor
FETCH NEXT FROM @cGetLicenseId INTO @iLicenseId, @iLicenseSubscriptionDetailId
-- end of while loop
END
-- close the cursor to free up resources
CLOSE @cGetLicenseId
DEALLOCATE @cGetLicenseId
-- Selecting the desired result from temporary table
SELECT AccountNumber, LicenseeName, LicenseType, Edition, ISNULL(CONVERT(VARCHAR,RenewalDate,101),'') as RenewDate
FROM #NetAdDetailReport
-- Dropping the temporary table
DROP TABLE #NetAdDetailReport
END
GO