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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading