Home » SQL & PL/SQL » SQL & PL/SQL » Issues on financial year (multiple merges)
Issues on financial year (multiple merges) [message #309092] Wed, 26 March 2008 07:20 Go to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi

how to create financial year means april-08 to march-09.
I have only date column. By using this date column who can i create financial year.
Re: How to create financial year . [message #309098 is a reply to message #309092] Wed, 26 March 2008 07:27 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What do you mean by "creating a financial year"? How do you create any other kind of year? What is this "date column" supposed to contain (other than a date (obviously))?
Re: How to create financial year . [message #309099 is a reply to message #309092] Wed, 26 March 2008 07:28 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Didn't understand your requirment.Do you want to generate dates from apr-08 to mar-09, then search for 'calendar' in this forum.


regards,
Re: How to create financial year . [message #309140 is a reply to message #309099] Wed, 26 March 2008 09:46 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi

any days between 01-apr-2007 to 31-mar-2008 will be
represented as Financial Year of 2007-08.

any days between 01-apr-2008 to 31-mar-2009 will be
represented as Financial Year of 2008-09.

In India various business year has been accounted for
deriving profit/loss balance sheet of the firm etc.

Like wise calender year means 1st Jan to 31st Dec

Academic year means 1st Jun to 31st may

and so many year combinations prevails. Popular
Financial Year was considered for tax assessment purpose.

am i exceed my limits?

sorry

yours
dr.s.raghunathan
Re: How to create financial year . [message #309142 is a reply to message #309140] Wed, 26 March 2008 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as ( 
  3      select to_date('01/01/2008','DD/MM/YYYY')+15*(level-1) d
  4      from dual
  5      connect by level <= 25
  6    )
  7  select d day, extract(year from add_months(d,-3)) fiscal_year
  8  from data
  9  /
DAY        FISCAL_YEAR
---------- -----------
01/01/2008        2007
16/01/2008        2007
31/01/2008        2007
15/02/2008        2007
01/03/2008        2007
16/03/2008        2007
31/03/2008        2007
15/04/2008        2008
30/04/2008        2008
15/05/2008        2008
30/05/2008        2008
14/06/2008        2008
29/06/2008        2008
14/07/2008        2008
29/07/2008        2008
13/08/2008        2008
28/08/2008        2008
12/09/2008        2008
27/09/2008        2008
12/10/2008        2008
27/10/2008        2008
11/11/2008        2008
26/11/2008        2008
11/12/2008        2008
26/12/2008        2008

25 rows selected.

Regards
Michel
Re: How to create financial year . [message #309146 is a reply to message #309092] Wed, 26 March 2008 10:26 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
excellent...

hats off

yours
dr.s.raghunathan
problem in creating year [message #309293 is a reply to message #309092] Thu, 27 March 2008 01:10 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi

In my datadase i have date column is there, by using that one i want to create year column.

In year column user want to see the values like
2005-2006
2006-2007
2007-2008
How can i create this one.Plz help me.

note:Date column
containg data from 01-Jan-2005 to 31-Dec-2012.
Re: problem in creating year [message #309297 is a reply to message #309293] Thu, 27 March 2008 01:34 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Use the TO_CHAR() with the '||' operator.


regards,
Re: problem in creating year [message #309299 is a reply to message #309293] Thu, 27 March 2008 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is just a follow-up to your previous topic, just use what is showed in it and post what you tried.

Regards
Michel
Problem in Financial year [message #310363 is a reply to message #309092] Tue, 01 April 2008 00:54 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi,

This query which i am using to genrate financial year.I give 2008 it gives the values from Apr-08 to Mar-09.
But this qury showing only Apr-08 to Dec-08, I am not able to see the remaing months.
Any one check it and give the sloution for this issues.

SELECT DISTINCT
to_char(summary_DATE,'Mon-YY')
FROM
CALENDAR_date
WHERE summary_date BETWEEN to_date('01-APR-'|| TO_NUMBER(TO_CHAR(summary_DATE,'YYYY')) ,'DD-MON-YYYY')
and to_date('31-MAR'|| (TO_NUMBER(TO_CHAR(summary_DATE,'YYYY'))+1) ,'DD-MON-YYYY')
AND TO_NUMBER(TO_CHAR(summary_DATE,'YYYY'))='2008'
Re: Problem in Financial year [message #310370 is a reply to message #310363] Tue, 01 April 2008 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We already answered to this question last week.
Please read the answers and STOP creating topic on the same question.

Regards
Michel

[Updated on: Tue, 01 April 2008 01:07]

Report message to a moderator

Re: Issues on financial year (multiple merges) [message #310379 is a reply to message #309092] Tue, 01 April 2008 01:15 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> AND TO_NUMBER(TO_CHAR(summary_DATE,'YYYY'))='2008'

This condition filters out rows from calendar year 2009.
If you want to include them, just remove the condition.
Or do you have any other special reason for issuing it?
Re: Problem in Financial year [message #310380 is a reply to message #310363] Tue, 01 April 2008 01:16 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
tHANKS
IAM NT UNDERSTAND your QUERY WHICH U POST.
cAN U CHECK THE QUERY WHICH I POST TO YOU.
Re: Problem in Financial year [message #310384 is a reply to message #310380] Tue, 01 April 2008 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DON'T SHOUT.
Your query is wrong.

Regards
Michel
Re: Problem in Financial year [message #310385 is a reply to message #310380] Tue, 01 April 2008 01:25 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
oK, pLEASE send the correct query.
Re: Problem in Financial year [message #310393 is a reply to message #310385] Tue, 01 April 2008 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The query I sent gives the fiscal year from a date.
Use this expression.

Regards
Michel
Re: Problem in Financial year [message #310399 is a reply to message #310385] Tue, 01 April 2008 01:53 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
I am not that much strong in sql.
I am not under stand this query. Can u please explain me.


with
2 data as (
3 select to_date('01/01/2008','DD/MM/YYYY')+15*(level-1) d
4 from dual
5 connect by level <= 25
6 )
7 select d day, extract(year from add_months(d,-3)) fiscal_year
8 from data
Re: Problem in Financial year [message #310400 is a reply to message #310399] Tue, 01 April 2008 01:58 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use code tags, I use them, use them also, you should how to do it now.

The first part just generates dates, the 2 last lines display these dates along with their associated fiscal year. Use the expression in it to find your fiscal.

Regards
Michel

[Updated on: Tue, 01 April 2008 02:01]

Report message to a moderator

Previous Topic: Getting single date ? (merged)
Next Topic: Constraint Error
Goto Forum:
  


Current Time: Tue Dec 06 08:21:23 CST 2016

Total time taken to generate the page: 0.17407 seconds