Operating System - HP-UX
1748081 Members
5189 Online
108758 Solutions
New Discussion юеВ

select week and year 1 year in the past

 
SOLVED
Go to solution
Petr Simik_1
Valued Contributor

select week and year 1 year in the past

Hello,
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>??
23 REPLIES 23
Peter Godron
Honored Contributor

Re: select week and year 1 year in the past

Petr,
can you use (modified):
select max(sysdate),add_months(max(sysdate),-12) from dual;
Regards
Fred Ruffet
Honored Contributor

Re: select week and year 1 year in the past

How is your date stored in your table ? Is it a varchar or a date field ?

Regards,

Fred
--

"Reality is just a point of view." (P. K. D.)
Petr Simik_1
Valued Contributor

Re: select week and year 1 year in the past

thnkx add_month is great but i cannot use it bc i dont have date format!!

I have 2 columns they are numbers. I need to select last record find 12 months older one.

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????

Eric Antunes
Honored Contributor

Re: select week and year 1 year in the past

Hi Petr,

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
Each and every day is a good day to learn.
Peter Godron
Honored Contributor

Re: select week and year 1 year in the past

Petr/Eric,
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!
Elmar P. Kolkman
Honored Contributor

Re: select week and year 1 year in the past

If the date in your own table is in the format 'yyyyww' I think you can do it like this:
...
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.

Every problem has at least one solution. Only some solutions are harder to find.
Petr Simik_1
Valued Contributor

Re: select week and year 1 year in the past

hi Eric,
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
Eric Antunes
Honored Contributor

Re: select week and year 1 year in the past

Peter,

I'm not using to_date, I'm using to_char.

Each and every day is a good day to learn.
Petr Simik_1
Valued Contributor

Re: select week and year 1 year in the past

Elmar.
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.