SQL Returning results for FIRST occurrence in a time-frame

I want to return only the very first Process dated transaction for each User ID in this designated time-frame that was within 1 year of the purchase_date.

From what we have:

User ID     Transaction Purchase D  Process Date
ZZ1316567   11004463720 10/4/2018   3/9/2020
ZZ1316567   41004401606 10/4/2018   1/9/2019
ZZ1316567   41004413203 10/4/2018   5/10/2019
ZZ1316567   41004447152 10/4/2018   10/23/2019
ZZ1316567   41004462755 10/4/2018   10/23/2019
VV1454366   41004457424 4/27/2017   12/11/2019
VV1454366   51004365427 4/27/2017   8/10/2018
VV1454366   11004401749 3/24/2017   12/20/2018
SS1456449   41004315438 3/24/2017   10/31/2017
SS1456449   41004323671 3/24/2017   12/14/2017
SS1456449   41004329229 3/24/2017   1/15/2018
SS1456449   41004356097 3/24/2017   6/11/2018
SS1456449   51004292754 3/24/2017   6/7/2017
SS1456449   51004334495 3/24/2017   1/15/2018
TT1474544   11004409317 6/30/2017   4/14/2019
TT1474544   41004428284 6/30/2017   8/7/2019

This:

    Select *

    From (
        Select Distinct USER_ID, Transaction, Purchase_date, Process_date
        Row_Number() OVER (PARTITION BY USER_ID ORDER BY Transaction) AS Uniqueness
        From Table1
        Where Purchase_date between DATEADD(YEAR,-4,GetDate()) and GetDate() 
        AND Purchase_date < DATEADD(year, 1, process_date)
    ) As Z

    Where Uniqueness = 1

What I want is it restricted to THIS as these BELOW are the only transactions from the list that had their transactions process date within 1 year of the purchase date and were first among those transactions even if there was another one for the same user within that time-frame i.e. exclude ANY and EVERYTHING that does not fit this criteria:

User ID     Transaction Purchase Date   Process Date
ZZ1316567   41004401606 10/4/2018       1/9/2019
SS1456449   51004292754 3/24/2017       6/7/2017

What am I missing? Am I going about this wrong?

Answer

The only issue is of ‘swapping of Purchase_date and Process_date‘.

Just change like following and you should get the desired result:

AND process_date < DATEADD(year, 1, Purchase_date)

Also, please use Process_date in the order by as suggested by @avery_larry.

Advertisement

Answer