GetCancelledLicenses.sql
13 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
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[GetCancelledLicenses] Script Date: 4/25/2017 1:27:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetCancelledLicenses]
-- Add the parameters for the stored procedure here
@sFromDate varchar(20), @sToDate varchar(20), @iStartPrice numeric(14,2), @iEndPrice numeric(14,2), @iLicenseTypeId tinyint,
@iAccountTypeId tinyint , @sZip varchar(20) = '', @iStateId int, @iCountryId int
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 @iLicenseId INT
DECLARE @iLicenseSubscriptionDetail INT
DECLARE @sAccountNumber VARCHAR(50)
DECLARE @sLicenseeName VARCHAR(100)
DECLARE @sLicenseType VARCHAR(50)
DECLARE @sInstitutionName VARCHAR(100)
DECLARE @dtStartDate DATETIME
DECLARE @dtEndDate DATETIME
DECLARE @dtLicenseCreationDate DATETIME
DECLARE @mSubscriptionPrice MONEY
DECLARE @sAccountType VARCHAR(50)
DECLARE @sEdition VARCHAR(200)
DECLARE @iCardNumber INT
-- 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 desired results of cancelled licenses on the basis of parameter
CREATE TABLE #CancelledLicenseReport
(
AccountNumber VARCHAR(50),
LicenseeName VARCHAR(100),
LicenseType VARCHAR(50),
InstitutionName VARCHAR(100),
Edition VARCHAR(200),
ValidFrom DATETIME,
ValidThrough DATETIME,
LicenseCreationDate DATETIME,
Price MONEY,
AccountType VARCHAR(50),
CardNumber INT
)
-- define the forward only, read-only cursor
SET @cGetLicenseID = CURSOR FAST_FORWARD
FOR
SELECT LicenseSubscriptionDetail.LicenseId, MAX(LicenseSubscriptionDetail.Id)
FROM LicenseSubscriptionDetail
INNER JOIN License ON License.Id = LicenseSubscriptionDetail.LicenseId
WHERE (License.CancellationDate 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 LicenseSubscriptionDetail.LicenseId
--HAVING (MAX(SubscriptionValidThrough) BETWEEN @dtFromDate AND @dtToDate)
-- open & fetch the cursor variables into the local variables
OPEN @cGetLicenseID
FETCH NEXT FROM @cGetLicenseID INTO @iLicenseId, @iLicenseSubscriptionDetail
-- start of while loop
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sEdition = ''
-- fetch the accountnumber, licenseename, licensetype, startdate, enddate, subscriptionprice, accountype of a license
SELECT @sAccountNumber = AccountNumber, @sLicenseeName = LicenseeName, @sLicenseType = LicenseType,
@sInstitutionName = InstitutionName,
@dtStartDate = SubscriptionValidFrom, @dtEndDate = SubscriptionValidThrough,
@dtLicenseCreationDate = CreationDate,
@mSubscriptionPrice = TotalAmount, @sAccountType = AccountType, @iCardNumber = CardNumber
FROM
(
SELECT AccountNumber, (LicenseeFirstName+' '+LicenseeLastName) as LicenseeName,
LicenseType.Title as LicenseType, License.InstitutionName,
AccountType.Title as AccountType, LicenseSubscriptionDetail.TotalAmount,
LicenseSubscriptionDetail.SubscriptionValidFrom, LicenseSubscriptionDetail.SubscriptionValidThrough,
License.CreationDate,
DATEDIFF(dd,GETDATE(),License.CancellationDate) as DaysRemaining, (CASE WHEN License.CardNumber > 0 THEN License.CardNumber END) as CardNumber
FROM License
INNER JOIN LicenseType ON License.LicenseTypeId = LicenseType.Id
INNER JOIN AccountType ON License.AccountTypeId = AccountType.Id
INNER JOIN State ON License.StateId = State.Id
INNER JOIN Country ON License.CountryId = Country.Id
INNER JOIN LicenseSubscriptionDetail ON License.Id = LicenseSubscriptionDetail.LicenseId
WHERE License.IsActive = 0
AND License.LicenseTypeId = (CASE WHEN @iLicenseTypeId > 0 THEN @iLicenseTypeId ELSE License.LicenseTypeId END)
AND License.AccountTypeId = (CASE WHEN @iAccountTypeId > 0 THEN @iAccountTypeId ELSE License.AccountTypeId END)
AND State.Id = (CASE WHEN @iStateId > 0 THEN @iStateId ELSE State.Id END)
AND Country.Id = (CASE WHEN @iCountryId > 0 THEN @iCountryId ELSE Country.Id END)
AND License.Zip = (CASE WHEN LEN(@sZip)>0 THEN @sZip ELSE License.Zip END)
AND LicenseSubscriptionDetail.Id = @iLicenseSubscriptionDetail
AND License.LicenseTypeId <> 5
) t1
-- check whether the above query returns any row
IF @@Rowcount > 0
BEGIN
-- 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
--AMI SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1);
IF LEN(@sEdition)> 1
-- remove the trailing comma-separator from the edition-string
SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1)
ELSE
SET @sEdition = @sEdition
-- insert into the temporary table
INSERT INTO #CancelledLicenseReport
(AccountNumber,LicenseeName,LicenseType,InstitutionName,Edition,ValidFrom,ValidThrough,LicenseCreationDate,Price,AccountType,CardNumber)
VALUES(@sAccountNumber,@sLicenseeName,@sLicenseType,@sInstitutionName,@sEdition,@dtStartDate,@dtEndDate,@dtLicenseCreationDate,@mSubscriptionPrice,@sAccountType,@iCardNumber)
END
-- fetch the next record from cursor
FETCH NEXT FROM @cGetLicenseID INTO @iLicenseId,@iLicenseSubscriptionDetail
-- 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,InstitutionName,AccountType, Edition, CONVERT(VARCHAR,ValidFrom,101) as StartDate,
CONVERT(VARCHAR,ValidThrough,101) as EndDate,CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate, CONVERT(NUMERIC(14,2),Price) as SubscriptionPrice, CardNumber
FROM #CancelledLicenseReport ORDER BY AccountNumber
-- Dropping the temporary table
DROP TABLE #CancelledLicenseReport
END