RFM.sql

USE BOOKING_EXPORT
GO

/* BORRA LAS TABLAS */

IF OBJECT_ID('tempdb..#R')	IS NOT NULL	
DROP TABLE  #R

IF OBJECT_ID('tempdb..#F')	IS NOT NULL	
DROP TABLE  #F

IF OBJECT_ID('tempdb..#M')	IS NOT NULL	
DROP TABLE  #M

IF OBJECT_ID('MATRIX')		IS NOT NULL	
DROP TABLE  MATRIX

IF OBJECT_ID('RFM')			IS NOT NULL	
DROP TABLE  RFM

/* DECLARACIONES */

DECLARE @P  INT
DECLARE @P1 FLOAT
DECLARE @V  INT

/* TABLA QUE ALMACENA LOS VALORES DE LA MATRIZ  RFM */

CREATE TABLE MATRIX (
	ID INT ,
	PERCENTIL FLOAT,
	VALOR INT
)

/* LLENA LA MATRIZ */

INSERT INTO  MATRIX VALUES (4, 25, 4)
INSERT INTO  MATRIX VALUES (3, 50, 3)
INSERT INTO  MATRIX VALUES (2, 75, 2)
INSERT INTO  MATRIX VALUES (1, 100, 1)


/* GENERA LAS TABLAS INDIVIDUALES PARA R, F Y M */

 SELECT COUNT (CUSTOMER) AS F, 0 AS VALOR, CUSTOMER
 INTO #F 
 FROM [RFM-SAMPLE-ORDERS-SQL] 
 GROUP BY CUSTOMER 

 SELECT MIN (Q.R) AS R, 
		0 AS VALOR,
		Q.CUSTOMER
INTO #R
FROM (
 SELECT  DATEDIFF(DAY, CONVERT(VARCHAR, CONVERT(DATE,ORDER_DATE,101)), CONVERT(VARCHAR,GETDATE(),101)) AS R
        ,CUSTOMER

 FROM [RFM-SAMPLE-ORDERS-SQL] 
 GROUP BY CUSTOMER , ORDER_DATE
 )Q 
 GROUP BY Q.CUSTOMER


 SELECT SUM(CONVERT(INT,GRAND_TOTAL)) AS M,0 AS VALOR, CUSTOMER
 INTO #M
 FROM [RFM-SAMPLE-ORDERS-SQL] 
 GROUP BY CUSTOMER


SET @P = ( SELECT MAX(ID) FROM MATRIX )

WHILE @P >= 1 
BEGIN

	SET @P1 = (SELECT PERCENTIL FROM MATRIX WHERE ID = @P)
	SET @V  = (SELECT VALOR FROM MATRIX WHERE ID = @P)
	UPDATE K 
	SET K.VALOR = @V
	FROM (
	SELECT TOP (@P1) PERCENT R, CUSTOMER 
	FROM #R 
	ORDER BY R ASC ) T
	LEFT JOIN #R K ON T.CUSTOMER = K.CUSTOMER
	WHERE VALOR = 0

	UPDATE K 
	SET K.VALOR = @V
	FROM (
	SELECT TOP (@P1) PERCENT F, CUSTOMER 
	FROM #F 
	ORDER BY F DESC ) T
	LEFT JOIN #F K ON T.CUSTOMER = K.CUSTOMER
	WHERE VALOR = 0

	UPDATE K 
	SET K.VALOR = @V
	FROM (
	SELECT TOP (@P1) PERCENT M, CUSTOMER 
	FROM #M
	ORDER BY M DESC ) T
	LEFT JOIN #M K ON T.CUSTOMER = K.CUSTOMER
	WHERE K.VALOR = 0

	SET @P= @P-1
END

SELECT M.CUSTOMER, 
       R.VALOR AS  R, 
	   F.VALOR AS F, 
	   M.VALOR AS M, 
	   M.VALOR + R.VALOR + F.VALOR AS VALOR_SUM,
	   CONCAT (M.VALOR , R.VALOR, F.VALOR) AS VALOR_CONCAT
INTO RFM
FROM #M M
LEFT JOIN #F F ON M.CUSTOMER = F.CUSTOMER
LEFT JOIN #R R ON M.CUSTOMER = R.CUSTOMER 

SELECT * FROM RFM ORDER BY VALOR_SUM DESC