Improve company productivity with a Business Account.Sign Up

x
?
Solved

FiscalYearDateQuerrySQL

Posted on 2011-03-24
3
Medium Priority
?
226 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
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

595 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