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.

This was last published in August 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close