Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Three consecutive months in fiscal year

## I have a conveyance_claim table with claim date and type, one record per month. I have to check in financial year (April to March) if three continuous months have claims of the same type. How can I find it?

I have a conveyance_claim table with claim date and type (C=car, B=bike, etc.), one record per month. I have to check in financial year (April to March) if three continuous months have claims of the same type. How can I find it?

Fiscal years are traditionally named after the calendar year in which they end. So the year that runs from April 2005 to March 2006 is called Fiscal 2006 (even though most of it is in calendar 2005). However, all of this is a red herring.

We don't need to do anything special with fiscal months. We can refer to them by their ordinary calendar year and month numbers. All we need to do is find three consecutive ones in a row. This is accomplished with a three-table self-join.

```select t1.c_date  as claimdate1
, t2.c_date  as claimdate2
, t3.c_date  as claimdate3
from conveyance_claim as t1
inner
join conveyance_claim as t2
on t1.c_type = t2.c_type
and year(t1.c_date)*12+month(t1.c_date)
= year(t2.c_date)*12+month(t2.c_date) - 1
inner
join conveyance_claim as t3
on t2.c_type = t3.c_type
and year(t2.c_date)*12+month(t2.c_date)
= year(t3.c_date)*12+month(t3.c_date) - 1```

The expressions which multiply the year by 12 and add the month are intended only to produce a sequential number, which we will not need to display, but which will allow us to subtract 1 from January 2005, for example, and be able to compare it successfully to December 2004.

To run this query so that it takes effect only within a certain fiscal year, use a condition like this:

``` where t1.c_date >= '2005-04-01'
and t1.c_date < '2006-02-01'```

The date range may be restricted to April to February, because after February a three-consecutive-month run cannot occur in that fiscal year.

Close