DAYOFWEEK, Every Sunday

Question

At work we need to produce a report that is 6 month old. Basically, from May 1st to Nov 1st, and the report has to be weekly based. So far, I am only be able to do it one week at a time manually...

This is my code

SELECT displayname, capture_time,
       satisfied_load_time AS load_time,
       satisfied_response_time AS apdex_response_time

FROM   session_page_summary, page, apdex_value 

WHERE  displayname LIKE 'xyz%'    
      AND capture_time >= '2012-11-01 00:00:00' 
      AND capture_time <= '2012-11-07 00:00:00'    
      AND page.page_id = session_page_summary.page_id    
      AND page.page_id = apdex_value.page_id

GROUP BY displayname 
ORDER BY displayname

I stripped out some of the long code just to make it look slightly better.

The result is...

displayname  capture_time  loadtime  responsetime         
>xyz-a         11/7/2012     0.12       1.34
>xyz-b         11/7/2012     0.83       2.92
>xyz-c         11/7/2012     0.56       1.02

How can I get a result containing 6 months of data, something like this?

displayname  capture_time  loadtime  responsetime          
>xyz-a         11/7/2012     0.72       1.82
>xyz-b         11/15/2012    1.23       1.01
>xyz-c         11/22/2012    2.83       0.78

every 7 days I get the report.

Answer 1

The YEARWEEK function is probably what you need to group by week. It has a number of options to define how the week starts or what year it's classified in.

Your query would also benefit from using the BETWEEN operator. x BETWEEN a AND b is usually more efficient than x > a AND x < b.