How to group values according to the weeks of the year ?
Hi,
I have a table with dates and values (I simplify for the need of this question)
I want to group the values (and summing them) according to the 52 weeks of a year. I mean grouping week for week.
Here are the date of each weeks generated for one year, it gives all the mondays of each week of the year :
select trunc(trunc(sysdate,'year'),'iw')+(rownum*7)-7 n from all_objects where rownum <= 53
What I want to do is to sum up the values of my table and group them into each week they pertain to. These weeks date are generated by the query above.
For example all the values falling into the 1st week of the year will be grouped into this date, all the values falling into the 2nd wek will be grouped into this date ans so on. If no values are found for a week, then a 0 value is generated, in iorder to have a 52 weeks-lines. It is basically a kind of select with "date between .... and ..." and grouping the values and affecting them to the number of the wee they do pertain.
Examples of mondays 30/06/2008, 07/07/2008, 14/07/2008
Example of values and dates into my table :
Datte Value
30/06/08 9
01/07/08 9
02/07/08 9
03/07/08 9
03/07/08 9
04/07/08 6
04/07/08 6
04/07/08 9
07/07/08 9
08/07/08 9
29/07/08 9
29/07/08 6
30/07/08 9
31/07/08 9
31/07/08 9
31/07/08 9
01/08/08 9
04/08/08 5,5
04/08/08 9
07/08/08 9
07/08/08 9
08/08/08 9
14/08/08 9
14/08/08 9
18/08/08 9
22/08/08 9
22/08/08 9
25/08/08 9
25/08/08 9
26/08/08 9
29/08/08 9
29/08/08 9
01/09/08 9
01/09/08 9
04/09/08 9
04/09/08 9
05/09/08 9
05/09/08 9
05/09/08 9
05/09/08 9
17/09/08 9
Result should be something like this :
Number of the week Date Value
1 31/12/2008 47
2 07/12/2008 94
3 14/01/2008 23
and so on....
.....
52 22/12/2008 73
53 29/12/2008 60
Hope I am clear. Thank you for your kind answers.