Operating System - HP-UX
1748181 Members
4212 Online
108759 Solutions
New Discussion юеВ

Re: select week and year 1 year in the past

 
SOLVED
Go to solution
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