Check if a record exist in previous month ONLY in SQL

Question

I am working on a project to classify customers as 'existing' or 'new' in MS SQL server.

The rule for determining is to check the customer was with the company LAST month. Sample format below:

The expected result below:

enter image description here

Customer A is an existing customer in Dec-22 and Jan-23 because it is in the table in the previous of these two months. However, you will find customer B appears twice as a NEW customer because there is no record of customer B in the month prior.

P.S. the month prior for customer B here is referring to Nov 2022 and Jan 2023, because there is no record of customer B in these two months. Customer B will need to be classified as NEW customer

May I know how to achieve the result?

Any suggestion is appreciated!

Thank you

I have done research on the internet and try to test myself. nothing worked

Answer 1

You could probably do something like this:

SELECT  *
,   CASE 
        WHEN EOMONTH(DATEADD(MONTH, -1, Date)) = EOMONTH(LAG(Date) OVER(PARTITION BY customer ORDER BY date)) THEN 'Exist'
        ELSE 'New'
    END
  FROM  (
    SELECT  'A', '20221110'
    UNION ALL
    SELECT  'A', '20221231'
    UNION ALL
    SELECT  'A', '20230115'
    UNION ALL
    SELECT  'B', '20221231'
    UNION ALL
    SELECT  'B', '20230220'
) x (Customer, Date)

LAG(Date) allows to get previous date (partitioned by customer). Then just to be on a safe side, i convert the date to last day of month and compare to previous month's date. If it's a match, it means customer already exists, otherwise he's marked as new, this also handles the case where there's no previous date at all