CODEWITHSUNDEEP
sqloracle-databasedatetimegroup-bycount
Mike
Mike

Reputation: 4435

Group Data by Year, Oracle SQL

I am trying to create a query that counts records that existed within a year. The table looks like this:

Title_ID   ISSUE_DATE   EXPIRY_DATE   CLIENT_NUMBER
123        '26-JUN-19'  '17-AUG-20'   8529
124        '04-APR-19'  '17-SEP-22'   8529
125        '09-MAY-15'  '11-SEP-19'   3654
126        '31-DEC-19'  '25-NOV-22'   9852
127        '27-OCT-18'  '26-FEB-21'   2254
128        '05-OCT-11'  '01-JAN-19'   9852

Specifically, I want to count the number of distinct CLIENT_NUMBERS of the records that existed in a given calendar year.

The record (title) exists from the ISSUE_DATE until the EXPIRY_DATE. If the record existed at any point within a year (Let's say 2019), then we are interested in including it in our client count.

So, if the record was issued in 2019 or if the record expired in 2019 or if the record was issued before 2019 and expired after 2019, then we are interested in including it in the client count for the year it existed.

I have built the following query that does this, but only for one specific year (2019). I'd like to build the query further so it look at each calendar year and counts the distinct client numbers when the client has an active title:

SELECT *
--  count(distinct client_number)
FROM
  TITLE
WHERE
  issue_date between '01-Jan-19' and '31-Dec-19'
  or expiry_date between '01-Jan-19' and '31-Dec-19'
  or (issue_date < '01-Jan-19' and expiry_date > '31-Dec-19')

Where I am having trouble is, my data is much larger than the subset I have provided. I would like to recursively get counts of distinct client numbers by year using the same kind of logic to include a record within a calendar year as I have outlined above. So, I'd like to have a table like this:

YEAR    COUNT_OF_CLIENT_NUMBERS
2020    5469
2019    5587
2018    4852
2017    4501
2016    3265
etc

I think I've stretched by current SQL abilities at this point, so I thought Id ask to see if there are any suggestions to make this happen?

Thanks.

EDIT: to clarify, the issue date and the expiry date apply to the title, not the client. So, the title is issued on the issue date and expires on the expiry date. A client can own one or more title(s).

So, I am looking to get a count of how many distinct clients own active titles within a give year if one or more of their titles is active within that year. So the key is, a title is considered active if it was issued in that year OR it expired within that year OR it was issued before that year and expired after that year. A title CAN be active in multiple years (i.e. Issued on Feb. 4, 2014 and expires on Apr.7 2017, I want to include the client count for each year that titles exists....2014, 2015, 2016 and 2017).

So, I created a table to join to (thanks @GMB for the suggestion):

with calendar_year (y) as 
  (
  select 2010 from dual
  union all select y + 1 from calendar_year where y < 2020
  )
  
select * from calendar_year

Which returns:

2010
2011
2012
2013
2014
etc

I want to join that to my titles table, but I am having issues recursively looking at the issue date and expiry date to join up the title to each year it existed in. Any help in that area, would be great!

Upvotes: 1

Views: 757

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can get number of clients on any day by unpivoting the data, so there is one row per date. Then keep track of the "ins" and "outs".

You don't specify the database, but here is one approach:

select dte, sum(inc),
       sum(sum(inc)) over (order by dte) as active_on_date
from ((select issue_date as dte, 1 as inc
       from t
      ) union all
      (select expiry_date as dte, -1 as inc
       from t
      ) 
     ) t
group by dte
order by dte;

EDIT:

Hmmm, the above may not do exactly what you want. If you want to count distinct client numbers rather than overall rows, then it might be simpler to just list the dates and join:

select d.dte, count(distinct t.client_id)
from (select date '2020-01-01' as dte from dual union all
      select date '2019-01-01' as dte from dual union all
      select date '2018-01-01' as dte from dual union all
      . . .
     ) d left join
     t
     on d.dte between t.issue_dte and t.expiry_dte
group by d.dte
order by d.dte;

Upvotes: 1

GMB
GMB

Reputation: 222672

You can use a recursive query to generate the years, then bring the table with a left join, and aggregate:

with dates (dt) as (
    select date '2016-01-01' from dual
    union all select add_months(dt, 1) from dates where dt < date '2020-01-01'
)
select d.dt, count(distinct t.client_number) count_of_client_numbers
from dates d
left join title t 
    on t.issue_date <= d.dt
    and t.expiry_date > d.dt
group by d.dt

The upside of this approach is that you get results for each and every year, even those where no title started or ended.

Upvotes: 1

Related Questions