- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: select week and year 1 year in the past
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2005 12:04 AM
тАО01-27-2005 12:04 AM
I am looking for query in Oracle how to select year and week from database 1 year in the past.
I want to make avg and sum over last 1 year data. In DB I have rows year and week.
My idea is to pick last date record in db which is usually 2 weeks old.
I get max date record by this query:
select max(year*100+week) as max_date from table
than I want find year and week which is 12 month in the past. I know that there is
add_months (date, -12) function but how to put there date from previous record>??
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2005 12:16 AM
тАО01-27-2005 12:16 AM
Re: select week and year 1 year in the past
can you use (modified):
select max(sysdate),add_months(max(sysdate),-12) from dual;
Regards
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2005 12:21 AM
тАО01-27-2005 12:21 AM
Re: select week and year 1 year in the past
Regards,
Fred
"Reality is just a point of view." (P. K. D.)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2005 07:51 PM
тАО01-27-2005 07:51 PM
Re: select week and year 1 year in the past
I have 2 columns
Probably the issue could be translate the year,week into date format than I can use date functions.
.... to_date(year*100+week,yyyyww) but this doesnt work.
Or I tried to use aritmetical solution.
mod((week-12),52) to find correnct week
and than somehow pick previous year if necessary,
Do u have any advice????
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2005 08:02 PM
тАО01-27-2005 08:02 PM
Re: select week and year 1 year in the past
Here is what you want, supposing that your year and week columns are dates:
select max( to_char( week, 'WW'))||'-'||max( to_char( year, 'YYYY')) as max_date
from table
where to_char( sysdate, 'WW') = '04' and
to_char( add_months(sysdate, -12), 'YYYY') = '2004'
Best Regards,
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2005 08:16 PM
тАО01-27-2005 08:16 PM
Re: select week and year 1 year in the past
I think the major problem is that the reformat using WW in to_date results in the ORA-1820 problem: metalink 271651.1
Please correct me!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2005 08:16 PM
тАО01-27-2005 08:16 PM
Re: select week and year 1 year in the past
...
where datecol = to_char(addmonths(sysdate,-12),'YYYYWW')
DON'T start calculation on your column values, but do it on sysdate instead... otherwise you would do calculations per table row, instead of once when the query is being parsed.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2005 08:21 PM
тАО01-27-2005 08:21 PM
Re: select week and year 1 year in the past
there is not necessary to tranform columns into char ww yyyy
select to_char(year,'yyyy'), to_char(week,'ww')
is useless bc year and week are in num format
this is my table descr.
WEEK NUMBER,
YEAR NUMBER,
DEPT VARCHAR2(50),
SERVICE VARCHAR2(255),
AVAILABILITY FLOAT(63),
REASON NVARCHAR2(2000),
MINUTESDOWN NUMBER,
NAME VARCHAR2(255)
i need to select
1) data over last 1 year
2) data over last 3 months
thnkx. P
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2005 08:22 PM
тАО01-27-2005 08:22 PM
Re: select week and year 1 year in the past
I'm not using to_date, I'm using to_char.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-27-2005 08:23 PM
тАО01-27-2005 08:23 PM
Re: select week and year 1 year in the past
yes this is the solution I tried too.
But problem is that I have in my table usualy last data from 1-2 week in the past. than using sysdate - 12 month has a discrepancy within this 2 weeks.