Tagging if the Client is new or old in SQL

Transact-SQL
1

I have here a query that shows three columns::

SELECT CLIENT_NO, LOB_CD ,TO_DATE(CREATE_DT,'MMYYYY') AS PURCHASE_DT ,ROW_NUMBER () OVER(PARTITION BY CLIENT_NOORDER BY CREATE_DT ASC) AS RN  FROM CLIENTS_T

now, I have to solve this:

  • if the client has 2 RN and either purchase date is below 2019, then the tag should be "OLD Client"
  • If the client has 1 RN and the purchase date is below 2019, then should still be tagged as "Old Client" else, if the purchase date is above 2019 then should be tagged as "2019 new client" granted that no purchase was done in 2020, 2021 and 2022.
  • same goes with 2020, 2021 and 2022. If there are no purchase the prior year, they should be tagged as "NEW".

Is there a way to come up with this? here is the table output, with desired tagging with remarks:

Result
Result864×297 46.2 KB

Hope someone can help.

Thank you

2

Please post directly usable sample data, i.e., CREATE TABLE and INSERT statement(s) rather than just an image of data. We can't write SQL against an image :smiley:

3

Hi Scott,

Is this okay? I just pasted my sample data from excel...

CLIENT_NO LOB_CD PURCHASE_DATE RN TAG
1 M 1-Apr-01 1 OLD
1 I 1-Jun-85 2 OLD
2 I 1-Dec-81 1 OLD
6 I 1-Jun-94 1 OLD
7 I 1-Jun-94 1 OLD
7 M 1-Jun-20 2 OLD
8 M 1-Jun-19 2 NEW
9 M 1-Jun-19 1 NEW
9 M 1-Jul-19 1 NEW
10 I 1-Oct-21 1 OLD
10 I 1-Oct-22 2 OLD
11 M 1-Jul-20 1 NEW
4

Hope someone can help

5

what is 2 RN?

6 
CASE WHEN 
    Rn = 2 
	   AND 
	YEAR(Purchase_Date) < 2019 
THEN 
   'OLD Client'
7

You should know by now to provide consumable test data with dates in ISO format.

CREATE TABLE #t
(
	Client_No int NOT NULL
	,Purchase_date date NOT NULL
	,PRIMARY KEY (Client_No, Purchase_date)
);
INSERT INTO #t
VALUES (1, '19010401')
	,(1, '19850601')
	,(2, '19811201')
	,(6, '19940601')
	,(7, '19940601')
	,(7, '20200601')
	,(8, '20190601')
	,(9, '20190601')
	,(9, '20190701')
	,(10, '20211001')
	,(10, '20221001')
	,(11, '20200701');

Your rules are far from clear and I am not convinced about their asymmetric nature.
As the rules do seem to be based on the year of purchase per client the following should provide a starting point:

WITH ClientYears
AS
(
	SELECT DISTINCT Client_No
		,DATEADD(year, DATEDIFF(year, 0, Purchase_date), 0) AS Purchase_Year
	FROM #t
)
,YearDetails
AS
(
	SELECT Client_No, Purchase_Year
		,MIN(Purchase_Year) OVER (PARTITION BY Client_No) AS Min_Purchase_Year
		,LEAD(Purchase_Year, 1, '9999') OVER (PARTITION BY Client_No ORDER BY Purchase_Year) AS Next_Purchase_Year
		,LAG(Purchase_Year, 1, '1900') OVER (PARTITION BY Client_No ORDER BY Purchase_Year) AS Prev_Purchase_Year
	FROM ClientYears
)
,Tags
AS
(
	SELECT Client_No, Purchase_Year
		,CASE
			WHEN Min_Purchase_Year < '2019'
			THEN 'Old'
			WHEN DATEDIFF(year, Purchase_Year, Next_Purchase_Year) <= 3
			THEN 'Old'
			WHEN DATEDIFF(year, Prev_Purchase_Year, Purchase_Year) = 1
			THEN 'Old'
			ELSE 'New'
		END AS Tag
	FROM YearDetails
)
SELECT T.Client_No, T.Purchase_date, S.Tag
FROM #t T
	JOIN Tags S
		ON T.Client_No = S.Client_No
			AND T.Purchase_date >= S.Purchase_Year
			AND T.Purchase_date < DATEADD(year, 1, S.Purchase_Year);