/PATTON/597RoyalCUCommercial/Stored Procedures/dbo.spExpirePointsExtractRev01.StoredProcedure.sql

USE [597RoyalCUCommercial]
GO
/****** Object:  StoredProcedure [dbo].[spExpirePointsExtractRev01]    Script Date: 09/25/2009 14:41:59 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************************************************/
/*    This will Create the Expired Points Table for Monthly Processing          */
/*     This process will take the current monthenddate and subtract 3 years     */
/*     All points older than this date are subject to expiration                */
/*    %%%%%% Note This version of the procedure creates the expiring points     */
/*           table based on the current month end year minus 3 to expire points */
/*           this table is then used BY spProcessExpiredPoints to update the    */
/*           Customer and History Tables                                        */
/* BY:  B.QUINN                                                                 */
/* DATE: 3/2007                                                                 */
/* REVISION: 0                                                                  */
/*                                                                              */
/********************************************************************************/
CREATE PROCEDURE [dbo].[spExpirePointsExtractRev01] @MonthEndDate NVARCHAR(10)  AS   

--declare @MonthEndDate NVARCHAR(10)  
--set @MonthEndDate='11/30/2009'  

declare @DateOfExpire NVARCHAR(10), @ExpireYear char(4)
set @ExpireYear=right( @MonthEndDate, 4)
SET @DateOfExpire = '12/31/' + @ExpireYear
Declare @PointsEarned int
Declare @Pointsredeemed int
Declare @PointsToExpire int
DECLARE @SQLUpdate nvarchar(1000) 
DECLARE @SQLDynamic nvarchar(1000)
declare @expirationdate nvarchar(25)
declare @intday int
declare @intmonth int
declare @intyear int
declare @ExpireDate DATETIME


set @ExpireDate = cast(@DateOfExpire as datetime)

SET @intYear = DATEPART(year, @ExpireDate)
SET @intmonth = DATEPART(month, @ExpireDate)
SET @intday = DATEPART(day, @ExpireDate)
set @intYear = @intYear - 5
set @expirationdate = (rtrim(@intYear) + '-' + rtrim(@intmonth) + '-' + rtrim(@intday) +   ' 23:59:59.997')


/*print 'year'
print @intYear
print 'month'
print @intmonth
print 'day'
print @intday
print '@expirationdate'
print @expirationdate */

	TRUNCATE TABLE ExpiredPoints

	INSERT   into ExpiredPoints
	 SELECT tipnumber, sum(points * ratio) as addpoints,
	'0' AS REDPOINTS, '0' AS POINTSTOEXPIRE, '0' as PREVEXPIRED, @ExpireDate as dateofexpire
	from history
	where histdate < @expirationdate and (trancode not like('R%') and
	     trancode <> 'IR'and trancode not like('XP'))
	group by tipnumber


	UPDATE ExpiredPoints  
	SET PREVEXPIRED = (SELECT SUM(POINTS* RATIO) 
		 FROM HISTORY 
		 WHERE
  		 (trancode  ='XP') 
	  	 AND TIPNUMBER = ExpiredPoints.TIPNUMBER) 
	WHERE EXISTS  (SELECT *
		 FROM HISTORY 
		 WHERE
  		  trancode  ='XP'
	  	 AND TIPNUMBER = ExpiredPoints.TIPNUMBER) 

	UPDATE ExpiredPoints  
	SET REDPOINTS = (SELECT SUM(POINTS* RATIO) 
		 FROM HISTORY 
		 WHERE
  		 (trancode  like('R%') or
   		  trancode = 'IR')  
	  	 AND TIPNUMBER = ExpiredPoints.TIPNUMBER) 
	WHERE EXISTS  (SELECT *
		 FROM HISTORY 
		 WHERE
  		 (trancode  like('R%') or
   		  trancode = 'IR')  
	  	 AND TIPNUMBER = ExpiredPoints.TIPNUMBER)


	UPDATE ExpiredPoints  
	SET POINTSTOEXPIRE = (ADDPOINTS + REDPOINTS + PREVEXPIRED),
	    dateofexpire = @ExpireDate


	UPDATE ExpiredPoints  
	SET POINTSTOEXPIRE = '0'
	WHERE
	POINTSTOEXPIRE IS NULL 

	UPDATE ExpiredPoints  
	SET POINTSTOEXPIRE = '0'
	WHERE
	POINTSTOEXPIRE < '0' 

	delete from ExpiredPoints 	
	WHERE
	POINTSTOEXPIRE = '0'

update monthly_statement_file
set PointsToExpire='0'

update monthly_statement_file
set PointsToExpire=b.pointstoexpire
from monthly_statement_file a, expiredpoints b
where a.tipnumber=b.tipnumber
GO