USE [AIADatabaseV5] GO /****** Object: StoredProcedure [dbo].[GetCustomerSummary] Script Date: 04/25/2017 16:38:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetCustomerSummary] -- Add the parameters for the stored procedure here @sAccoutNumber varchar(50)='', @sLicenseeFullName varchar(100)='', @iStartPrice numeric(14,2), @iEndPrice numeric(14,2), @iLicenseType tinyint, @iAccountType tinyint, @sZip varchar(20) = '', @iState int, @iCountry 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 @cGetLicenseDetails CURSOR DECLARE @iLicenseId INT DECLARE @sAccountNumber VARCHAR(50) DECLARE @sLicenseeName VARCHAR(100) DECLARE @iLicenseTypeId TINYINT DECLARE @sLicenseType VARCHAR(50) DECLARE @dtStartDate DATETIME DECLARE @dtEndDate DATETIME DECLARE @sAccountType VARCHAR(50) DECLARE @iAccountTypeId TINYINT DECLARE @sLicenseStatus VARCHAR(8) DECLARE @sEdition VARCHAR(200) DECLARE @bExists bit DECLARE @sLicenseState VARCHAR(50) DECLARE @sLicenseZip VARCHAR(20) DECLARE @sLicenseCountry VARCHAR(50) DECLARE @sInstitutionName VARCHAR(100) DECLARE @dtLicenseCreationDate DATETIME DECLARE @mSubscriptionPrice MONEY DECLARE @iLicenseSubscriptionId INT DECLARE @sEmailId VARCHAR(100) DECLARE @iCardNumber INT -- create a temporary table to store the desired results of licenses on the basis of parameter CREATE TABLE #CustomerReport ( AccountNumber VARCHAR(50), LicenseeName VARCHAR(100), LicenseType VARCHAR(50), Edition VARCHAR(200), Email VARCHAR(100), ValidFrom DATETIME, ValidThrough DATETIME, AccountType VARCHAR(50), LicenseStatus VARCHAR(8), Price MONEY, LicenseState VARCHAR(50), LicenseZip VARCHAR(20), LicenseCountry VARCHAR(50), InstitutionName VARCHAR(100), LicenseCreationDate DATETIME, CardNumber INT ) SET @sLicenseeFullName = REPLACE(@sLicenseeFullName,' ',' OR ') -- define the forward only, read-only cursor SET @cGetLicenseDetails = CURSOR FAST_FORWARD FOR SELECT License.Id, License.AccountNumber, (License.LicenseeFirstName+' '+License.LicenseeLastName), License.LicenseTypeId, License.AccountTypeId, License.EmailId, (CASE License.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) as LicenseStatus, State.StateName, License.Zip, Country.CountryName, License.InstitutionName,License.CreationDate, (CASE WHEN License.CardNumber > 0 THEN License.CardNumber END) as CardNumber FROM License INNER JOIN State ON License.StateId = State.Id INNER JOIN Country ON License.CountryId = Country.Id WHERE License.AccountNumber = (CASE WHEN LEN(@sAccoutNumber)>0 THEN @sAccoutNumber ELSE License.AccountNumber END) AND License.LicenseTypeId = (CASE WHEN @iLicenseType > 0 THEN @iLicenseType ELSE License.LicenseTypeId END) AND License.AccountTypeId = (CASE WHEN @iAccountType > 0 THEN @iAccountType ELSE License.AccountTypeId END) AND State.Id = (CASE WHEN @iState > 0 THEN @iState ELSE State.Id END) AND Country.Id = (CASE WHEN @iCountry > 0 THEN @iCountry ELSE Country.Id END) AND License.Zip = (CASE WHEN LEN(@sZip)>0 THEN @sZip ELSE License.Zip END) -- open & fetch the cursor variables into the local variables OPEN @cGetLicenseDetails FETCH NEXT FROM @cGetLicenseDetails INTO @iLicenseId, @sAccountNumber, @sLicenseeName, @iLicenseTypeId, @iAccountTypeId, @sEmailId, @sLicenseStatus, @sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName,@dtLicenseCreationDate, @iCardNumber -- start of while loop WHILE @@FETCH_STATUS = 0 BEGIN SET @sEdition = '' SET @bExists = 1 -- fetch the latest license start/end date of a license on the basis of Subscription Start & End price if any SELECT @dtStartDate = MAX(LicenseSubscriptionDetail.SubscriptionValidFrom), @dtEndDate = MAX(LicenseSubscriptionDetail.SubscriptionValidThrough), @iLicenseSubscriptionId = MAX(LicenseSubscriptionDetail.Id) FROM LicenseSubscriptionDetail WHERE LicenseSubscriptionDetail.LicenseId = @iLicenseId 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 -- check whether the above query returns any row IF @@Rowcount > 0 BEGIN -- check whether the name of licensse matches the name entered by user IF LEN(@sLicenseeFullName) > 0 BEGIN SELECT @bExists = 1 FROM License WHERE Id = @iLicenseId AND (LicenseeFirstName LIKE '%'+@sLicenseeFullName+'%' OR LicenseeLastName LIKE '%'+@sLicenseeFullName+'%') --CONTAINS((LicenseeFirstName,LicenseeLastName) IF @@Rowcount = 0 BEGIN SET @bExists = 0 END END -- check whether the above query returns any row IF @bExists = 1 BEGIN -- fetch the licensetype of the license SELECT @sLicenseType = LicenseType.Title FROM LicenseType WHERE LicenseType.Id = @iLicenseTypeId -- fetch the accounttype of the license SELECT @sAccountType = AccountType.Title FROM AccountType WHERE AccountType.Id = @iAccountTypeId -- 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 -- fetch the price of the license SELECT @mSubscriptionPrice = TotalAmount FROM LicenseSubscriptionDetail WHERE Id = @iLicenseSubscriptionId -- insert into the temporary table INSERT INTO #CustomerReport (AccountNumber, LicenseeName, LicenseType, Edition, Email, ValidFrom, ValidThrough, AccountType, LicenseStatus, Price, LicenseState, LicenseZip, LicenseCountry, InstitutionName, LicenseCreationDate, CardNumber) VALUES(@sAccountNumber, @sLicenseeName, @sLicenseType, @sEdition, @sEmailId, @dtStartDate, @dtEndDate, @sAccountType, @sLicenseStatus, @mSubscriptionPrice, @sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName,@dtLicenseCreationDate, @iCardNumber) END END -- fetch the next record from cursor FETCH NEXT FROM @cGetLicenseDetails INTO @iLicenseId, @sAccountNumber, @sLicenseeName, @iLicenseTypeId, @iAccountTypeId, @sEmailId, @sLicenseStatus, @sLicenseState, @sLicenseZip, @sLicenseCountry, @sInstitutionName, @dtLicenseCreationDate, @iCardNumber -- end of while loop END -- close the cursor to free up resources CLOSE @cGetLicenseDetails DEALLOCATE @cGetLicenseDetails -- Selecting the desired result from temporary table SELECT AccountNumber, LicenseeName, LicenseType, AccountType, Edition, Email, CONVERT(VARCHAR,ValidFrom,101) as StartDate, CONVERT(VARCHAR,ValidThrough,101) as EndDate, LicenseStatus, CONVERT(NUMERIC(14,2),Price) as Price, LicenseZip, LicenseState, LicenseCountry,InstitutionName, CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate , CardNumber FROM #CustomerReport ORDER BY AccountNumber -- Dropping the temporary table DROP TABLE #CustomerReport END