# Find last record in last 12 months of current record

• cidr

Ten Centuries

Points: 1293

I need to build a solution and for brevity I'm going to use the example of customers buying products.

I want to return, for the current full year (in this case 2018) customer records in the year (2018) that bought a product in the previous 12 months of that purchase.
This means I'm looking at a rolling 12 month (potentially 24 months of data 2017-01-01- 2018-12-31).
example:
Customer A buys a product at 2018-08-10.  If customer has bought a product before this in the previous 12 months from 2017-08-10- 2018-08-10, Return the previous purchase record and so on. If customer A buys a product on 2018-01-01 (first day of current full year), then I'm looking as far back as 2017-01-01.

``` Customer purchasedate Lastpurchasedate  DaysDifference repeat count RecStatus c1 20/12/2018 04/12/2018 16 8 c1 04/12/2018 25/07/2018 132 7 c1 25/07/2018 01/03/2018 146 6 c1 01/03/2018 05/02/2018 24 5 c1 05/02/2018 25/01/2018 11 4 c1 25/01/2018 05/01/2018 20 3 c1 05/01/2018 01/05/2017 249 2 c1 01/05/2017 01/01/2017 120 1 c1 01/01/2017 null 0 delete c2 20/12/2018 04/12/2018 16 6 c2 04/12/2018 25/07/2018 132 5 c2 01/08/2018 05/02/2018 177 4 c2 25/07/2018 01/03/2018 146 3 c2 05/07/2018 25/01/2018 161 2 c2 05/05/2018 01/05/2017 369 1 c2 01/02/2017 10/01/2017 22 0 delete c2 10/01/2017 null 0 delete ```

You can see that for Customer C1, the first date of purchase in the current year is 2018-05-01.  there was a record made in the previous 12 months from this date which is 2018-05-01.  This means 2018-05-01 is included and is the first purchase.  As long as the subsequent purchases are in the current year and within 12 months of each other the repeat count increments.

Customer C2s first record in the current month is 2018-05-05.  However the last purchase record is 2017-02-01 which is more than 12 months so 2018-05-05 is the first record since it's in the current year.

I hope this all makes sense.  Ideally I'd like to have a similar result set to the table above.  I've tried the following as an example but this is not working as i can't delete the records the records mentioned above.
Any help would be very appreciated.

`FROM (         SELECT Row_number() OVER( partition BY customer ORDER BY purchasedate) AS rn, * FROM data        WHERE purchasedate between '2017-01-01' and '2019-01-01'         ) a    LEFT JOIN      (         SELECT Row_number() OVER( partition BY customer ORDER BY purchasedate) AS rn, * FROM data        WHERE purchasedate between '2018-01-01' and '2019-01-01'         ) b      ON b.rn + 1 = a.rn      AND a.customer = b.customer `

• Steve Jones - SSC Editor

SSC Guru

Points: 715107

I'm not sure I understand. Are you looking for only 1 or 2 records per customer? Meaning a purchase within the last year (or calendar year) and possibly a purchase in the 12 months before that. Are you returning all purchases in 2018?

I would tackle this initially in a series of CTEs to organize the logic and before you're getting the results you want. I'd get a specific set of test data and results you want, especially at the boundary cases.

I think that you would get a list of customers that have had a purchase in the last year (or 2018). It's not quite clear if you mean calendar for the last purchase. From there, I'd use that list of customers and orders and search for an order that is within a dateadd(year, -1) of that date. That lets you get two lists of things. You could order those, but depending on how deeply you filter (all orders, 1, 2, etc), it may be more complex.

Posting  some DDL and test data would be good. Show specific results with your explanation.

• drew.allen

SSC Guru

Points: 76493

First, this is not the best way to post sample data.  I've created a quick and dirty method of doing it, because I'm a bit short on time.

`SELECT *INTO #dataFROM (VALUES    ('c1', CAST('20181220' AS DATE), CAST('20181204' AS DATE), 16, 8, NULL),    ('c1', '20181204', '20180725', 132, 7, NULL),    ('c1', '20180725', '20180301', 146, 6, NULL),    ('c1', '20180301', '20180205', 24, 5, NULL),    ('c1', '20180205', '20180125', 11, 4, NULL),    ('c1', '20180125', '20180105', 20, 3, NULL),    ('c1', '20180105', '20170501', 249, 2, NULL),    ('c1', '20170501', '20170101', 120, 1, NULL),    ('c1', '20170101', null, NULL, 0, 'delete'),    ('c2', '20181220', '20181204', 16, 6, NULL),    ('c2', '20181204', '20180725', 132, 5, NULL),    ('c2', '20180801', '20180205', 177, 4, NULL),    ('c2', '20180725', '20180301', 146, 3, NULL),    ('c2', '20180705', '20180125', 161, 2, NULL),    ('c2', '20180505', '20170501', 369, 1, NULL),    ('c2', '20170201', '20170110', 22, 0, 'delete'),    ('c2', '20170110', null, NULL, 0, 'delete'))v (Customer, purchasedate, Lastpurchasedate, DaysDifference, repeat_count, RecStatus)`

I also wasn't sure exactly what you were looking for with your ranges.  I interpreted it as customers who hadn't visited in 12 months as having "lapsed" and being considered "new" customers.

`WITH new_returning AS(    SELECT *,         CASE WHEN LEAD(purchasedate, 1, '9999-12-30') OVER(PARTITION BY Customer ORDER BY purchasedate) > DATEADD(YEAR, 1, purchasedate) THEN 1 ELSE 0 END AS new_returning_customer    FROM #data), customer_groups AS(    SELECT *, SUM(new_returning_customer) OVER(PARTITION BY Customer ORDER BY purchasedate DESC) AS grp    FROM new_returning)SELECT *FROM customer_groupsWHERE grp = 1`

I'm also not quite getting the results you are expecting, but it is close.  Looking at the data, it matches what I expect.

Drew

J. Drew Allen