dbo.GetExpiringLicenses.StoredProcedure.sql
13.1 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
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[GetExpiringLicenses] 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].[GetExpiringLicenses]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetExpiringLicenses]
GO
CREATE PROCEDURE [dbo].[GetExpiringLicenses]
-- Add the parameters for the stored procedure here
@sFromDate varchar(20), @sToDate varchar(20), @iStartPrice numeric(14,2), @iEndPrice numeric(14,2),
@iLicenseTypeId int, @iAccountTypeId int, @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 @dtLicenseCreationDate DATETIME
DECLARE @dtStartDate DATETIME
DECLARE @dtEndDate DATETIME
DECLARE @mSubscriptionPrice MONEY
DECLARE @sAccountType VARCHAR(50)
DECLARE @sEdition VARCHAR(200)
DECLARE @iDaysRemaining INT
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 license which are going to be expire on the basis of parameter
CREATE TABLE #ExpiringLicenseReport
(
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),
DaysRemaining INT,
CardNumber INT
)
-- define the forward only, read-only cursor
SET @cGetLicenseId = CURSOR FAST_FORWARD
FOR
SELECT LicenseSubscriptionDetail.LicenseId, MAX(LicenseSubscriptionDetail.Id)
FROM LicenseSubscriptionDetail WHERE
(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 & days remaining to expire for a license
SELECT @sAccountNumber = AccountNumber, @sLicenseeName = LicenseeName, @sLicenseType = LicenseType,
@sInstitutionName = InstitutionName, @dtLicenseCreationDate = CreationDate,
@dtStartDate = SubscriptionValidFrom, @dtEndDate = SubscriptionValidThrough,
@mSubscriptionPrice = TotalAmount, @sAccountType = AccountType, @iDaysRemaining = DaysRemaining, @iCardNumber = CardNumber
FROM
(
SELECT AccountNumber, (LicenseeFirstName+' '+LicenseeLastName) as LicenseeName,
LicenseType.Title as LicenseType, AccountType.Title as AccountType,
License.InstitutionName,License.CreationDate,
LicenseSubscriptionDetail.TotalAmount,
LicenseSubscriptionDetail.SubscriptionValidFrom, LicenseSubscriptionDetail.SubscriptionValidThrough,
DATEDIFF(dd,GETDATE(),LicenseSubscriptionDetail.SubscriptionValidThrough) 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 LicenseSubscriptionDetail ON License.Id = LicenseSubscriptionDetail.LicenseId
INNER JOIN State ON License.StateId = State.Id
INNER JOIN Country ON License.CountryId = Country.Id
WHERE License.IsActive = 1
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
WHERE DaysRemaining>=0
-- 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
SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1);
-- insert into the temporary table
INSERT INTO #ExpiringLicenseReport
(AccountNumber, LicenseeName, LicenseType,InstitutionName, Edition, ValidFrom, ValidThrough,LicenseCreationDate, Price, AccountType, DaysRemaining,CardNumber)
VALUES(@sAccountNumber,@sLicenseeName,@sLicenseType,@sInstitutionName,@sEdition,@dtStartDate,@dtEndDate,@dtLicenseCreationDate, @mSubscriptionPrice,@sAccountType,@iDaysRemaining,@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,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,AccountType,DaysRemaining, CardNumber
FROM #ExpiringLicenseReport ORDER BY AccountNumber
-- Dropping the temporary table
DROP TABLE #ExpiringLicenseReport
END
GO