Solved

FiscalYearDateQuerrySQL

Posted on 2011-03-24
3
219 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 500 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

680 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