General
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Re: select week and year 1 year in the past

Ok,

Data from the last year:

select max( week), max( year) as max_date
from table
where year in ( select max(year)-1 from table)

The data from the last 3 month, I'm afraid it is not possible because you don't have dates but number... Why do you use numbers to represent dates?? Dates can be transformed in seconds, mintutes, hours, days, weeks, months and years but numbers...

Best Regards,

Eric Antunes

Each and every day is a good day to learn.
Thierry Poels_1
Honored Contributor

Re: select week and year 1 year in the past

hi,

1. last year :
select sum(xxx), avg(xxx), ...
from table
where (year*100 + week) >
(select max((year-1)*100+week)
from table)

2. last 3 months:
define 3 months
can you live with 13 weeks which is more or less 3 months??

regards,
Thierry.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Eric Antunes
Honored Contributor

Re: select week and year 1 year in the past

"...because you don't have dates but numbers"... and those numbers represents only weeks and years but not months, so you you can have 12 week columns equal to 4 but you just don't know whitch of those 12 records refers to January for example...
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

Yhank u.
year stat is probably solved by
year*100+week > (select max(year*100+week)-100

3 months can be defined as 12 weeks.
As I see I should update table with 1 additional row where I include some date within the reported week it will make my live much easier.
By the rules thursday means 1 week I am going to put thursday beside every row with week.

3month probably has no reason to create.
If I need quartal sum and avg than it will be more logical to create fixed periods 1Q = week(1..12) 2Q=week(13-25) ......

Reason why there are weeks years as number format is bc this is imported data from excel. last 2 years people included data there and I decided to convert it into database for better/effective manipulation.
Yogeeraj_1
Honored Contributor

Re: select week and year 1 year in the past

hi,

try using rownum <4

hope this helps!

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: select week and year 1 year in the past

Ok,

So you have weeks from 1 to 52. I understood you had just from 1 to 4.

You see, to get data from Q1 it is much more correct to work with dates than with numbers because, for example, week 14 of 2004 as only 3 days in March (Q1) and has 4 days in April (Q2)...
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

Yeah this works ;-)).
I can do it by rownum ;-)

this is the syntax and it works.

select .... from table where year*100+week in (
select rownum,wy from (select distinct year*100+week as wy from table order by 1 desc) where rownum < 12)
Petr Simik_1
Valued Contributor

Re: select week and year 1 year in the past

Eric.
In my country has every week 7 days.
I have never heard of week with less than 7 days except last/firs weeks of year
Eric Antunes
Honored Contributor

Re: select week and year 1 year in the past

Petr,

You didn't understand my previous post... I'll try do explain better with 2 questions:

31-03-2004 is from witch week and quarter?

01-04-2004 is from witch week and quarter?


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

now I understand...sorry I ment not week in month but week number in year.

31-03-2004 is from witch week and quarter?
is week 14


01-04-2004 is from witch week and quarter?
is week 14

05-04-2004
is week 15


Eric Antunes
Honored Contributor

Re: select week and year 1 year in the past

31-03-2004 is week 14 and Q1

01-04-2004 is week 14 but Q2

Week 14 have some days in Q1 and some others in Q2. So it is not very correct to do a quarter analysis just with weeks. That't why you should consider having just a data column...

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Yogeeraj_1
Honored Contributor
Solution

Re: select week and year 1 year in the past

hi,

you can use the query below to clear all confusions about quarters!:

yd@MYDB.MU> r
1 select dt, to_char(dt,'Q'), mod(to_char(dt,'Q')+1,4)+1
2 from (select add_months(trunc(sysdate,'y'), rownum-1 ) dt
3* from all_objects where rownum <= 12 )

DT T MOD(TO_CHAR(DT,'Q')+1,4)+1
__________ _ __________________________
01/01/2005 1 3
01/02/2005 1 3
01/03/2005 1 3
01/04/2005 2 4
01/05/2005 2 4
01/06/2005 2 4
01/07/2005 3 1
01/08/2005 3 1
01/09/2005 3 1
01/10/2005 4 2
01/11/2005 4 2
01/12/2005 4 2

12 rows selected.

Elapsed: 00:00:00.02
yd@MYDB.MU>

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Thierry Poels_1
Honored Contributor

Re: select week and year 1 year in the past

hi,

This is about statistics reporting, so a variance might be accepted. It's not about for example interest on your bank account which you want to be correct to the day.

A real date might simplify things, but you can do without. It always depends; maybe you cannot/may not change the table.

regards,
Thierry.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Petr Simik_1
Valued Contributor

Re: select week and year 1 year in the past

Thank u for all advices they are all very conductive.

U r right that Q statistics are not very exact but I have my data only in week resolution than how to make a avg and sum over part of the week?

I can change that table however I want.
I just get this data from excel sheets and they are stored there in this format. I used the same format and thats it.


Mod function is very usefull it solves quite a lot ;-))