?
Solved

FiscalYearDateQuerrySQL

Posted on 2011-03-24
3
Medium Priority
?
222 Views
Last Modified: 2012-05-11
I have a stock item table like this


stock_item
-----------
stock_code  varchar2(10)
discontinued varchar2(1)
date_discontined  date

I want to report on items that are not disconitnued or that have been discontinued this fiscal year only. I want to exclude any items discontinued previous fiscal year or before.

Disconitnued = 'Y'
Not discontinued = NULL

What is the best sql for that. I was thinking of somethig like this

select stock_code from stock_item where discontinued IS NULL OR (discontinued='Y' and date_discontinued >= '01-OCT-'||to_char(to_char(trunc(sysdate('YYYY'))-1)

since there is no function that subtracts 3 months from 1st day of year.

ADD_MONTHS only adds months.
0
Comment
Question by:sam15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35212902
how about this?

add_months will work just fine


select stock_code from stock_item where discontinued IS NULL OR (discontinued='Y' and date_discontinued >=add_months(trunc(sysdate,'yyyy'),-3)
0
 

Author Comment

by:sam15
ID: 35216210
Yes, you are correct. I think i had wrong place of parenthesis for TO_CHAR.

you are a genius!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35216246
don't need to_char at all,  but if you were going to do that (and I don't recommend it)


it would look something like


 to_date( '01-OCT-'||(to_char(sysdate,'yyyy')-1),'dd-MON-yyyy')


or more formally

to_date( '01-OCT-'||to_char((to_number(to_char(sysdate,'yyyy'))-1)),'dd-MON-yyyy')
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question