How to create First Date Flag Column Per Customer/Client in SQL?-CodePudding

I have a table that looks like this in SQL Server:

Client_Name	Client_ID	Purchase_Date
John Dee	2563	11/15/2022
John Dee	2563	11/29/2022
Sara Spence	7985	4/17/2022
Sara Spence	7985	5/12/2022
Sara Spence	7985	7/15/2022
Mark Brown	8635	1/23/2021
Mark Brown	8635	1/30/2021
Mark Brown	8635	6/20/2021

I want a query to create a flag column to identify first purchase date per client, expecting the result like this:

Client_Name	Client_ID	Purchase_Date	First_Purchase_Flag
John Dee	2563	11/15/2022	1
John Dee	2563	11/29/2022	0
Sara Spence	7985	4/17/2022	1
Sara Spence	7985	5/12/2022	0
Sara Spence	7985	7/15/2022	0
Mark Brown	8635	1/23/2021	1
Mark Brown	8635	1/30/2021	0
Mark Brown	8635	6/20/2021	0

Is there any solution create that flag column using a case statement? I have thousands of rows and tens of columns in the table.

I have tried this but it doesn't seem to work.

CASE 
WHEN Purchase_Date = MIN(Purchase_Date) OVER (ORDER BY Client_ID) THEN 1 
ELSE 0 
END AS First_Purchase_Flag

Version

Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64) Jan 12 2022 22:30:08 Copyright (C) 2019 Microsoft Corporation
Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

CodePudding user response：

You can utilise a window function but like this:

CASE WHEN Purchase_Date = MIN(Purchase_Date) OVER (PARTITION BY Client_Id) THEN 1 ELSE 0 END

An alternative would be

CASE WHEN Purchase_Date = FIRST_VALUE(Purchase_Date) OVER (PARTITION by Client_Id order by Purchase_Date ) THEN 1 ELSE 0 END

In either case your are dealing with each Client_Id as a group so you partition by this.

CodePudding user response：

Hi for this case you will want to create a windowing function using row_number(). a windowing function will apply the function over a window, which you can think of as a group. In the below code the row_number() function is being applied over a group created by client_names

select
Client_Name,
Client_ID,
Purchase_Date,
row_number() over (partition by client_name order by Purchase_Date desc) as First_Purchase_Flag

then you can create a case statement to flag all rows with a number 1

select
Client_Name,
Client_ID,
Purchase_Date,
First_Purchase_Flag,
case when First_Purchase_Flag = 1 then 1 else 0 end as First_Purchase_Ind

CodePudding user response：

CREATE TABLE #TestTable
(
    Client_Name NVARCHAR(100),
    Client_ID INT,
    Purchase_Date DATE
)

INSERT INTO #TestTable (Client_Name,Client_ID,Purchase_Date)
SELECT * FROM
(
VALUES
    ('John Dee',2563,'11/15/2022'),
    ('John Dee',2563,'11/29/2022'),
    ('Sara Spence',7985,'4/17/2022'),
    ('Sara Spence',7985,'5/12/2022'),
    ('Sara Spence',7985,'7/15/2022'),
    ('Mark Brown',8635,'1/23/2021'),
    ('Mark Brown',8635,'1/30/2021'),
    ('Mark Brown',8635,'6/20/2021')
) As RawData (Client_Name,Client_ID,Purchase_Date)


;WITH CTE
AS
(
    SELECT 
        Client_Name,
        Client_ID,
        Purchase_Date,
        ROW_NUMBER() OVER (PARTITION BY Client_ID ORDER BY Purchase_Date ASC) AS PurchaseOrdinal
    FROM #TestTable
)

    SELECT 
        Client_Name,
        Client_ID,
        Purchase_Date,
        IIF(PurchaseOrdinal = 1, 1, 0) AS First_Purchase_Flag
    FROM CTE