SQL WKS SELECT COMMAND

95 pts.
Tags:
Oracle 7.3.1
SQL commands
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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
  • carlosdl
    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.
    69,920 pointsBadges:
    report
  • carlosdl
    Oh, and please don't create a new question to provide more information. Please continue the discussion here. Thanks,
    69,920 pointsBadges:
    report
  • 235235235
    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
    95 pointsBadges:
    report
  • carlosdl
    235235235, posting the same text from your original question doesn't help. Did you read my previous comments ?
    69,920 pointsBadges:
    report
  • 235235235
    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
    95 pointsBadges:
    report
  • 235235235
    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
    95 pointsBadges:
    report
  • carlosdl
    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 ?
    69,920 pointsBadges:
    report
  • 235235235
    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
    95 pointsBadges:
    report
  • carlosdl
    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.
    69,920 pointsBadges:
    report
  • 235235235
    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.
    95 pointsBadges:
    report
  • 235235235
    [...] Are you an Oracle expert? Show your expertise by helping 235235235 get information from a [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
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

Thanks! We'll email you when relevant content is added and updated.

Following