CODEWITHSUNDEEP
sqlsql-server
Jerem
Jerem

Reputation: 31

FInd records 1 year older/newer than dates given in the same column, for each ID#

I need to find if a customer has a subscription the previous year and the following year, and how many subscriptions were new or were canceled the following year.

Sample data:

ID Subscription year
1 2010
1 2011
1 2019
2 2011
2 2012
3 2010

Thinking of this approach: subtracting and adding 1 to the subscription year and seeing if the customer ID has another row that corresponds (ex. if no rows for year+1, customer had canceled the next year). Hoping for something like this:

ID Subscription year SubscribedPreviousYear SubscribedNextYear
1 2010 F T
1 2011 T F
1 2019 F F
2 2011 F T
2 2012 T F
3 2010 F F

Then counting the F's in SubscribedPreviousYear as new subscriptions (they are counted as new if customer did not have one the immediate previous year, even for existing customers) and F's in SubscribedNextYear as canceled subscriptions, to get something like this:

Year New (# F's in SubscribedPreviousYear) Canceled (# F's in SubscribedPreviousYear)
2010 2 1
2011 1 1
2012 0 1
2019 1 1

I had tried this code, modified from a similar MySQL question, but got 'F' for all rows.

select 
    t1.Id, cast(t1.year as date), 
    IIF((select count(*) from table t2 
        where t1.Id=t2.Id and 
        datediff(y, t2.year, t1.year)=1) <1, 'T','F') 
    as SubscribedPreviousYear 
from table t;

Upvotes: 3

Views: 60

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521429

I would use LEAD() and LAG() here:

SELECT id, year,
       CASE WHEN LAG(year) OVER (PARTITION BY id ORDER BY year) = year - 1
            THEN 'T' ELSE 'F' END AS SubscribedPreviousYear,
       CASE WHEN LEAD(year) OVER (PARTITION BY id ORDER BY year) = year + 1
            THEN 'T' ELSE 'F' END AS SubscribedNextYear
FROM yourTable
ORDER BY id, year;

To get the final result, we can aggregate by year:

WITH cte AS (
    SELECT *,
        LAG(year) OVER (PARTITION BY id ORDER BY year) AS year_lag,
        LEAD(year) OVER (PARTITION BY id ORDER BY year) AS year_lead
    FROM yourTable
)

SELECT year,
       COUNT(CASE WHEN year != year_lag + 1 THEN 1 END) AS [New],
       COUNT(CASE WHEN year != year_lead - 1 THEN 1 END) AS Cancelled
FROM cte
GROUP BY year
ORDER BY year;

Upvotes: 1

Related Questions