?
Solved

FiscalYearDateQuerrySQL

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

571 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