CODEWITHSUNDEEP
sqlsql-server
Bart
Bart

Reputation: 21

Compare dates , one table, mssql

I'm trying to prepare query which should give a information about customer status ( 1 - new customer, 0 - old customer). The criterias are very simple, the new customer is company with starts cooparate with us or company which start again cooperate with us ( last two year without any transactions( without current year from table) )

Table Customers:

CustNo     Year
----------------
610         2013
610         2016
610         2017
610         2019
61000333    2018
61000333    2019
61002463    2013
61002463    2014
61002463    2017
61004504    2013
61004504    2014
61004504    2015
61004504    2016
61004504    2019

Table Customers should look like this:

CustNo     Year      Status
---------------------------
610         2013        1
610         2016        1
610         2017        0
610         2019        0
61000333    2018        1
61000333    2019        0
61002463    2013        1
61002463    2014        0
61002463    2017        1
61004504    2013        1
61004504    2014        0
61004504    2015        0
61004504    2016        0     
61004504    2019        1

Upvotes: 1

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I think you just want lag() with a case expression:

select t.*,
       (case when lag(year) over (partition by custno order by year) >= year - 2
             then 0 else 1
        end)
from t;

Upvotes: 4

Related Questions