If 3+ visit in last one year then A.

If Atleast 1 visit in past 24 months and 1 visit in the past 12 months then B.

If 1 visit during last 12 months then C.

If 1 visit during previous 13-24 months Then D.

If 1 visit during last 25-36 months E.

If All Visits Older than 50 months then F

CREATE PROCEDURE [dbo].[sp_status]

AS

BEGIN

set nocount on

DECLARE @CUSTNO INT

DECLARE @TOTALCUST INT

DECLARE @CUSTINCRE INT =1

DECLARE @ACQUIREDDATE DATE

DECLARE @getdate-2 DATE = GETDATE()

DECLARE@CITY VARCHAR(100)

DECLARE @STARTDATE DATE

DECLARE @GETPREFDATE DATE

DECLARE @MEMBER VARCHAR(15)

DECLARE @OLDMEMBER VARCHAR(15)='N/A'

DECLARE @TOTALDIV INT

DECLARE @DIVINCRE INT

SET @TOTALCUST = (SELECT COUNT(CUSTNO)FROM #DISTINCTCUST)

WHILE(@TOTALCUST >= @CUSTINCRE)

BEGIN

SELECT @CUSTNO=CUSTNO,@ACQUIREDDATE=ACQUIREDDATE FROM #DISTINCTCUST WHERE CUSTCNT=@CUSTINCRE -- GET CUSTOMER

SELECT ROW_NUMBER()OVER(ORDER BY DIVISION)DIVNCNT, DIVISION INTO #DISTDIVISIONFROM #DISTINCTDIV WHERE CUSTNO=@CUSTNO group by DIVISION---GET DISINCT DIVISION AND DATE

SET @TOTALDIV = (SELECT COUNT(DISTINCT DIVISION)FROM #DISTDIVISION)------GET DISINCT DIVISION

Set @DIVINCRE =1

WHILE(@TOTALDIV >=@DIVINCRE)

BEGIN

SELECT @CITY=DIVISION FROM #DISTDIVISION WHERE DIVNCNT=@DIVINCRE

SELECT CUSTNO,DIVISION,DATEPROVIDED INTO #GETALLDATA FROM #DISTINCTDIV WHERE CUSTNO=@CUSTNO AND DIVISION=@CITY

SET @STARTDATE= (SELECT MIN(DATEPROVIDED) FROM #GETALLDATA WHERE CUSTNO=@CUSTNO AND DIVISION=@CITY)

INSERT INTO #DESTRECENCY VALUES(@CUSTNO,@CITY,'AAA', @ACQUIREDDATE, @ACQUIREDDATE)

WHILE(@GETDATE >=@STARTDATE)

BEGIN

SET @GETPREFDATE =(SELECT MAX(DATEPROVIDED) FROM #GETALLDATA WHERE DATEPROVIDED <=@STARTDATE)

SET @MEMBER= (SELECT CASE

WHEN SUM(ISNULL(CASE WHEN DATEDIFF(DAY,DATEPROVIDED,@STARTDATE) between 0 and 365 then 1 end,0)) >= 2

THEN 'A'

WHEN sum(ISNULL(CASE WHEN DATEDIFF(DAY,DATEPROVIDED, @STARTDATE) between 0 and 365 then 1 end,0)) >= 1 and

sum(ISNULL(case when DATEDIFF(DAY,DATEPROVIDED, @STARTDATE) between 366 and 730 then 1 end,0)) >= 1

THEN 'B'

WHEN MIN(ISNULL(DATEDIFF(DAY,DATEPROVIDED, @STARTDATE),0)) between 0 and 365

THEN 'C'

WHEN MIN(ISNULL(DATEDIFF(DAY,DATEPROVIDED, @STARTDATE),0)) between 366 and 730

THEN 'D'

WHEN MIN(ISNULL(DATEDIFF(DAY,DATEPROVIDED, @STARTDATE),0)) between 731 and 1095

THEN 'E'

WHEN MIN(ISNULL(DATEDIFF(DAY,DATEPROVIDED, @STARTDATE),0)) > 1095

THEN 'F'ELSE 'N/A'

END FROM #GETALLDATA WHERE CUSTNO=@CUSTNO AND DIVISION=@CITY AND

DATEPROVIDED <= @STARTDATE) --BETWEEN @GETPREFDATE and @STARTDATE

--GROUP BY CUSTNO,DIVISION)

IF ((@OLDMEMBER <> @MEMBER OR @GETPREFDATE = @STARTDATE))

BEGIN

SET @OLDMEMBER = @MEMBER

INSERT INTO #DESTRECENCYVALUES (@CUSTNO,@CITY,@MEMBER, @STARTDATE, @GETPREFDATE)

END

SET @STARTDATE =DATEADD(DAY,1,@STARTDATE)

END

DROP TABLE #GETALLDATA

SET @DIVINCRE = @DIVINCRE + 1

END

DROP TABLE #DISTDIVISION

SET @CUSTINCRE = @CUSTINCRE + 1

END

--select * from #DESTRECENCY

TRUNCATE TABLE #DESTRECENCY

DROP TABLE #DESTRECENCY

DROP TABLE #DISTINCTCUST

DROP TABLE #DISTINCTDIV

DROP TABLE #DSTDIV

DROP TABLE #DIVISION

END

GO