CODEWITHSUNDEEP
sqlsql-serverdatabase
user1615573
user1615573

Reputation: 315

Count records that span multiple date range

ACCOUNT   Amount     DATE
1     50        01-2010
1     100       03-2010
1     100       02-2011
2     100       01-2011
2     50        05-2011
2     50        09-2011
3     100       03-2012
3     100       03-2013

Is there a query structure that will allow me to count distinct accounts that has spanned current and past year? For example, account 1 has amounts in 2011 and 2010 so it should be counted once under 2011. Account 2 only has amounts in 2011 so it doesn't get counted while account 3 has amounts in 2013 and 2012, so it gets counted as 1 under 2013:

 2010   2011    2012    2013                
 0      1       0       1

Upvotes: 1

Views: 667

Answers (3)

mehdi lotfi
mehdi lotfi

Reputation: 11571

Use Below Query :

SELECT YEAR(T1.Date) AS D, COUNT(*) AS C
FROM YourTable AS T1
INNER JOIN YourTable T2 ON T2.Account = T1.Account AND YEAR(T2)=YEAR(T1)+1
GROUP BY T1.Account, YEAR(T1.Date)

Upvotes: 0

dfb
dfb

Reputation: 13289

Assuming you have a record id (call this ID)

SELECT COUNT(*),Year FROM Table t3
INNER JOIN (
   SELECT record_id, Year(t1.Date) as Year FROM Table t1
   INNER JOIN Table t2
   WHERE Year(t1.Date)-1=Year(t2.Date) AND t1.Account == t2.Account
) x ON x.record_id = t3.record_id
GROUP BY Year

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

First, you need to know the years where you have data for an account:

select account, year(date) as yr
from t
group by account, year(date)

Next, you need to see if two years are in sequence. You can do this in 2012 with lag/lead. Instead, we'll just use a self join:

with ay as (
     select account, year(date) as yr
     from t
     group by account, year(date)
    )
select ay.account, ay.yr
from ay join
     ay ayprev
     on ay.account = ayprev.account and
        ay.yr = ayprev.yr + 1

Next, if you want to count the number of accounts by year, just put this into an aggregation:

with ay as (
     select account, year(date) as yr
     from t
     group by account, year(date)
    )
select yr, count(*) as numaccounts
from (select ay.account, ay.yr
      from ay join
           ay ayprev
           on ay.account = ayprev.account and
              ay.yr = ayprev.yr + 1
     ) ayy
group by yr

Upvotes: 1

Related Questions