Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

GROUP BY LAST 5 WEEK

576637Aug 13 2007 — edited Oct 6 2008
Hi,
I need help to create a sql query to get the avg hours spent per job during the previous 5 weeks, and the avg hours spent per job for the current week for a given period(say for between Jun 2007 and Aug 2007)
I need to get this in one query.

The following fields are required in the output
Week number (YWW format),
Avg. hrs per job for every week
Avg. hrs per job for the previous 5 weeks (This will be 0 for the first 5 weeks)

This is a sample data:
JOB_ID JOB_FINISH_DATE JOB_HOURS
10 15-JUN-07 50
20 21-JUN-07 40
30 10-JUL-07 30
40 15-JUL-07 20
50 18-JUL-07 20
60 21-JUL-07 30
70 01-AUG-07 40
80 10-AUG-07 50
90 12-AUG-07 100

I am using oracle 10g ver 2

I tried using RANGE BETWEEN, BUT NO SUCCESS
avg(nvl(sum(job_info.job_hours),0)) OVER(
ORDER BY TO_NUMBER(to_char(job_info.JOB_FINISH_DATE,'YWW'))
RANGE BETWEEN
TO_NUMBER(to_char(trunc(job_info.JOB_FINISH_DATE-35,'day'),'YWW')) PRECEDING
AND TO_NUMBER(to_char(trunc(job_info.JOB_FINISH_DATE-7,'day')+6,'YWW')) FOLLOWING)
AS Man_hrs_5wk_avg


Appreciate your help.
Thanks.
Anto

Comments

Laurent Schneider
Avg. hrs per job for the previous 5 weeks
(This will be 0 for the first 5 weeks)
-- test data
with job_info as (
select 10 job_id,to_date('15-JUN-07','DD-MON-RR')JOB_FINISH_DATE,50 JOB_HOURS from dual union all
select 20,to_date('21-JUN-07','DD-MON-RR'),40 from dual union all
select 30,to_date('10-JUL-07','DD-MON-RR'),30 from dual union all
select 40,to_date('15-JUL-07','DD-MON-RR'),20 from dual union all
select 50,to_date('18-JUL-07','DD-MON-RR'),20 from dual union all
select 60,to_date('21-JUL-07','DD-MON-RR'),30 from dual union all
select 70,to_date('01-AUG-07','DD-MON-RR'),40 from dual union all
select 80,to_date('10-AUG-07','DD-MON-RR'),50 from dual union all
select 90,to_date('12-AUG-07','DD-MON-RR'),100 from dual)
-- your query begins here
-- vvvvvvvvvvvvvvvvvvvvvvvvvv
select 
  to_char(trunc(job_finish_date,'WW'),'YY-WW'),
  avg(job_hours),
  case when
    trunc(job_finish_date,'WW') - min(trunc(job_finish_date,'WW')) over () < 28
  then -- first 5 weeks
    0
  else  
    sum(sum(job_hours)) over (order by trunc(job_finish_date,'WW') range between 28 preceding and current row)/   
    sum(count(job_hours)) over (order by trunc(job_finish_date,'WW') range between 28 preceding and current row)  
  end avg5 
from JOB_INFO
group by
  trunc(job_finish_date,'WW')
; 

TO_CH AVG(JOB_HOURS)       AVG5
----- -------------- ----------
07-24             50          0
07-25             40          0
07-28             25         35
07-29             25         28
07-31             40         28
07-32             75 41.4285714
576637
Thanks a lot Laurant. That's exactly I was looking for. You are a genius.

I made a minor change to get the average for previous 5 weeks (which do not include the current week data)

select
to_char(trunc(job_finish_date,'WW'),'YY-WW'),
avg(job_hours),
case when
trunc(job_finish_date,'WW') - min(trunc(job_finish_date,'WW')) over () < 35
then -- first 5 weeks
0
else
sum(sum(job_hours)) over (order by trunc(job_finish_date,'WW') range between 35 preceding and 7 preceding)/
sum(count(job_hours)) over (order by trunc(job_finish_date,'WW') range between 35 preceding and 7 preceding)
end avg5
from JOB_INFO
group by
trunc(job_finish_date,'WW')
Laurent Schneider
you are welcome ! I am glad it works for you
Nicolas Gasparotto

sum(sum(job_hours)) over (order by trunc(job_finish_date,'WW') range between 35 preceding and 7 preceding)/
sum(count(job_hours)) over (order by trunc(job_finish_date,'WW') range between 35 preceding and 7 preceding)

Since you work on dates, you can manage dates into the windows clause :

    sum(sum(job_hours)) over (order by trunc(job_finish_date,'WW') range between interval '35' day preceding and interval '7' day preceding)/   
    sum(count(job_hours)) over (order by trunc(job_finish_date,'WW') range between interval '35' day preceding and interval '7' day preceding)

Nicolas.

576637
Nicholas,
Thanks for your suggestions.

Anto.
Laurent Schneider
well, since you work with dates, between 35 and 7 is of course in days... I would consider intervals when working with timestamps!
663246
Hi

I need explaination of this two lines, Please can u explain the line

sum(sum(job_hours)) over (order by trunc(job_finish_date,'WW') range between 35 preceding and 7 preceding)/
sum(count(job_hours)) over (order by trunc(job_finish_date,'WW') range between 35 preceding and 7 preceding)

thanks
bagavathi
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 3 2008
Added on Aug 13 2007
7 comments
14,932 views