What is the logic behind your first column ?

It seems that there is a one year + one month shift between records. Can you explain that ?

You will probably need a simple GROUP BY query, but some clarification may be needed before suggesting a command.

Oh, and please don’t create a new question to provide more information. Please continue the discussion here.

Thanks,

Thanks for your response.

Actually i am using Oracle 7.3.1 with Devloper 2000

I want to get information from a table name ‘bilmst’ having column name ‘a/c no.’ and

‘enterdate’ i want to collect information as follows

fy 01-07 No of A/Cs

to 30-06

2005-06 1500

2006-07 1000

2007-08 1200

2008-09 1100

i want to get information as example given above

my financial year from 01-07 to 30-06.

can i get information with select command as above format.

please guide.

Thank

Toqeer Akbar

Software/Hardware used:
SOFTWARE

235235235, posting the same text from your original question doesn’t help.

Did you read my previous comments ?

thanks

I want to group no of a/cs in financial year wise for example, how many a/cs had been added in the year 2005-06,2006-07 and so on, as my financial year starts from 1st-july-05 to 30-jun-06, and from 1st-july-06 to 30-jun-07 so the grouping of financial will be 2005-06 and 2006-07,2007-08 etc. may be you understands it well now

thanks

the grouping of no of a/cs year wise for example from 01-jan-05 to 31-dec-05 can be obtained for last ten years easily, but the grouping of a/cs as my financial year wise is difficult. in my department all information is taken from 01-jul-(year) to 30-Jun(year) wise.
hope you understand

thanks
well wisher

Maybe I didn’t make myself clear. You explained everything except what really needed clarification.

So, “2005-06” means “2005-2006″ ?

If I understood correctly, you could use a combination of functions to calculate the number of whole years since some specific date (say 01-jul-2000), and use that as a grouping criteria.

It will give you the information you want, but probably not in the format you want it:

SELECT TRUNC(MONTHS_BETWEEN(enterdate,'01JUL2000')/12),SUM("a/c no")
FROM bilmst
GROUP BY TRUNC(MONTHS_BETWEEN(enterdate,'01JUL2000')/12);

If you want to make it return the information in a more fancy format, you will have to sacrifice some performance.

This query, would return the year of the end of the financial year as the grouping criteria:

SELECT TO_CHAR(TRUNC(MONTHS_BETWEEN(enterdate,'01JUL2000')/12)+2001),SUM("a/c no")
FROM bilmst
GROUP BY TO_CHAR(TRUNC(MONTHS_BETWEEN(enterdate,'01JUL2000')/12)+2001)

And this one (less efficient as it performs the calculations twice), would probably give you in the format you want it:

SELECT TO_CHAR(TRUNC(MONTHS_BETWEEN(enterdate,'01JUL2000')/12)+2000)||'-'||TO_CHAR(TRUNC(MONTHS_BETWEEN(enterdate,'01JUL2000')/12)+2001),SUM("a/c no")
FROM bilmst
GROUP BY TO_CHAR(TRUNC(MONTHS_BETWEEN(enterdate,'01JUL2000')/12)+2000)||'-'||TO_CHAR(TRUNC(MONTHS_BETWEEN(enterdate,'01JUL2000')/12)+2001)

Is that what you meant ?

thanks for your keen interest
In our departments we collect the information financial year wise 2005-06 means that total no of a/cs which have been entered from 01-jul-2005 to 30-jun-2006 makes one financial year and the no of a/cs entered from 01-jul-2006 to 30-jun-2007 makes 2006-07 financial year and so on………… 2007-08, 2008-09 etc. The no of a/cs entered in such way we group financial year wise and the report is usually obtained for last ten financial year wise.Once again i repeat 2005-06 means one year, half last six month of 2005 from 01-jul-2005 to 31-dec-2005 and the next six month of 2006 from 1st-jan-2006 to 30-jun-2006 makes one financial year of 2005-06 and like this it continues.and hope you understand. so, i want to get the figure of last ten years for fy.2001-02 to fy 2010-11
thanks

235235235, I’m confused.

It seems that you don’t read more than the first couple of lines of my comments, and you just keep repeating things you have already said. We have a communication problem here.

I have posted some queries, but I guess you didn’t look at them.

Sorry, I can’t help you further.

Good luck.

yes i think we have communication problem. but thanks for helping dont be angry. i will try your quires after weak end and inform you then.
any way thanks a lot.