Results 1 to 14 of 14
  1. #1
    Join Date
    May 2003
    Posts
    34

    Unanswered: fiscal year data

    Hi All

    I have a problem at hand

    Fiscal year => 01-Apr-2003 to 31-Mar-2004
    or 01-Apr-2004 to 31-Mar-2005
    or 01-Apr-2002 to 31-Mar-2003

    I have a table with lots of other columns and 1 date column(datetest).
    I want a query something like this which will take the sysdate & give me two dates which I can use in the between clause of SQL select statement.

    say if my sysdate is 28-Aug-2004
    then i want a query which will give me a count of all rows from the table with datetest column which fall between 01st -Apr-2004 to 31st-Mar-2005.

    Now say if the sysdate is 10-Jan-2004 or 11-Feb-2004 or 12-Mar-2004
    then my query should give me a count of all rows from the table with datetest column which fall between 01st -Apr-2003 to 31st-Mar-2004


    Regards
    Manish

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It appears you have the specification well understood.
    Now it only a Small Matter Of Programming (SMOP) to implement it.
    I'm sure you'll post a solution within 24 hours.
    You're doing very well. Keep up the good work.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would not include writing sql within the scope of programming

    i hope you weren't hinting that the solution involves processing code outside of sql

    it can be done with date expressions
    Last edited by r937; 08-30-04 at 23:01.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i would not include writing sql within the scope of programming
    Some folks do consider writing SQL is programming; as in writing commands for the computer to execute and obtaining desired results.
    If it were 100% brain dead trivial to obtain desired results, there would be many fewer NEWBIE type questions.
    What may be trivial to you, can be braining warping torture for others.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i would not include writing sql within the scope of programming
    Some folks do consider writing SQL is programming; as in writing commands for the computer to execute and obtaining desired results.
    If it were 100% brain dead trivial to obtain desired results, there would be many fewer NEWBIE type questions.
    What may be trivial to you, can be brain warping torture for others.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sure manish feels the same way you do, which is why i didn't see your answer as being all that helpful
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2003
    Posts
    34

    is it possible to do this in 1 sql statement

    Hey

    How do I get this done with SQL & not using PLSQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please don't "Hey" me

    i'm working on it for ya

    what is it with everybody and their manners tonight?

    must be a full moon or something
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, here it is, i've tested it in, um, another database system

    (i do not have oracle so i cannot test it in oracle)

    i trust i've used the right builtin functions...
    Code:
     where datetest
       between to_date( floor( (year(sysdate)*12+month(sysdate)+8)/12. ) - 1 || '-04-01' , 'yyyy-mm-dd' ) 
           and to_date( floor( (year(sysdate)*12+month(sysdate)+8)/12. )     || '-03-31' , 'yyyy-mm-dd' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2003
    Posts
    34

    Thanks

    Hi R937

    Sorry. I did not intend to bug you with that Hey.
    Apologize for my rhetoric.

    Many thanks for sending the solution. You were of great help

    Thanks Once again

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you have a table of dates to test it against?

    i'd just like to know that what i think works on my system also works on yours
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Fiscal Year

    Hi,

    We have similar issues at work here. Mainly for reports, where we want to show reports for any fiscal year.

    I created a package called fiscal_cal to do the calendar arithmetic. Thus, given any date, it can give you the fiscal year, quarter, etc.

    In this particular case, a small procedure can be written that takes a date and returns the fiscal year's beginning and end.

    Or, you can write the SQL statement like this:

    Select ...
    From ...
    Where fiscal_cal.year(date_column) = '2003'

    I think that is what you may be trying to achieve.

    Here, fiscal_cal is the package name and year is a function within this package.

    One of the advantages of this approach is that, if, for whatever reason, you want to change the start and end of the fiscal year, you can do so in the package only. All your existing queries will give you the correct result even without doing anything!


    Hope that helps.

    Ravi

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a third method involves joining any date to a fiscal calendar table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Fscal Year

    Hi,

    One can also combine the table method with the package method.

    The package can be implmented using the table.

    The table would store the fiscal year, year start and year end. The package would figure out the fiscal year from the given date.


    Thus, any changes would only involve changing the table data.

    The date table is almost always used in data warehousing.


    Ravi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •