Extracting financial years overlapping with tenancy periods

Question

Given these tenancy contracts:

          2012        2013        2014        2015        2016
YR        |           |           |           |           |     
FIN_YR       | 2012-2013 | 2013-2014 | 2014-2015 | 2015-2016 |     
          ____________________________________________________
1         ----------------++++--------------------------------
2         -----+++++++++++++++++++++++++++++++++++++++--------
4         -----------------------------++++++++++++++++++-----

which lasted over these dates:

TENANCY_ID FROM       TO
---------- ---------- ----------
1          2013-05-02 2013-08-12
2          2012-06-22 2015-09-01
4          2014-06-03 2015-11-15

I want to produce a long table like:

TENANCY_ID Financial_Year
---------- --------------
1          2013-2014
2          2012-2013
2          2013-2014
2          2014-2015
2          2015-2016
4          2014-2015
4          2015-2016

where Financial_Year shows the financial years (1 Apr - 31 Mar) over which each tenancy, at least partly, lasted.

If relevant, db2, otherwise a generic solution would be fine.

This breaks down into two pieces: 1) generating the year->start/end mapping table, like @Razzka does (frankly, a `Calendar` table, which would include things like financial year start, and about a billion other things, is one of the most useful analysis tables you can have in a db). 2) a simple, solved, [overlapping date range check](http://stackoverflow.com/a/325964/812837). — Clockwork-Muse, Aug 15 '16 at 17:36

Razzka · Accepted Answer · 2016-08-15T11:07:16.150

1

Sorry, haven't got db2 at hand, here's example at Oracle:

with financial_years as (
      select to_char(r) || '-' || to_char(r + 1) as year, 
             to_date('01.04.' || to_char(r),'dd.mm.yyyy') as date_begin, 
             to_date('31.03.' || to_char(r + 1) || '23:59:59','dd.mm.yyyy hh24:mi:ss') as date_End
      from t_fin_year -- here's a table (year INT)
    )
    select y.year,
           t.id
    from t_tenancy t
    join financial_years y
      on y.date_begin between t.from and t.to
         OR y.date_end between t.from and t.to
    order by t.id, y.year;

The main idea is to join financial years with tenancy wia dates: if year start or end is between tenancy start\end, then tenancy belongs to this year.

edited Aug 15 '16 at 11:07

answered Aug 15 '16 at 10:17

Razzka

641
5
16

First, dates should never use inclusive upper bounds (`<=`), but exclusive ones (`<`), especially if the precision of the type can vary (for example - timestamps on pretty much all RDBMSs allow you to specify the number of decimal places for fractional seconds). So, never use `BETWEEN` for date/time/timestamp types. Second, this actually _doesn't_ return the correct results for tenancies with multiple (more than 2) years, since in those cases there are years in the middle that aren't part of the start/end data. – Clockwork-Muse Aug 15 '16 at 17:27

Extracting financial years overlapping with tenancy periods

1 Answers1