Orion Orion - 3 months ago 19
SQL Question

Extracting financial years overlapping with tenancy periods

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, , otherwise a generic solution would be fine.

Answer

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.