Oracle Weekly View Help

Question

Hi all;

my table;

Date       |  Cost

01.01.2010 |  100

02.01.2010 |  200

03.01.2010 |  300

04.01.2010 |  400

10.01.2010 |  800

11.01.2010 |  800

12.01.2010 |  800

25.01.2010 |  500

26.01.2010 |  500

05.02.2010 |  600

13.02.2010 |  700

15.02.2010 |  700

ı want to make "date between '01.01.2010' and '28.02.2010' " weekly view

Week 1 | Week 2 | week 3 | week . .. . 

1000   | 2400   |    0   | 32432.... . . 

How to make pls help thank you ?

Answer 1

select to_char(date, 'ww'), sum(cost)
from table
group by to_char(date, 'ww');

Or something along those lines should bring sums by week with the week number in the result. Link to Oracle 11g to_char syntax and link to format values. If that doesn't do it and you don't need the week number trunc(date, 'DAY') might be what you're looking for.

Answer 2

Not elegant solution, but its works...

   SELECT SUM(Week1) Week1, SUM(Week2) Week2 ... SUM(Week36) Week36,
   SUM(Week36) Week37
   FROM   (SELECT DECODE(WeekNo, 1, Cost, 0) Week1,
            DECODE(WeekNo, 2, Cost, 0) Week2,
            ...
            DECODE(WeekNo, 36, Cost, 0) Week36,
            DECODE(WeekNo, 37, Cost, 0) Week37
     FROM   (SELECT to_char(DateFrom, 'IW') WeekNo, SUM(cost) Cost
              FROM   (SELECT trunc(SYSDATE) + LEVEL - 1 DateFrom,
                              LEVEL * 100 Cost
                       FROM   dual
                       CONNECT BY LEVEL < 40)
              GROUP  BY to_char(DateFrom, 'IW')))

Answer 3

SQL> create table mytable (the_date,cost)
  2  as
  3  select date '2010-01-01', 100 from dual union all
  4  select date '2010-01-02', 200 from dual union all
  5  select date '2010-01-03', 300 from dual union all
  6  select date '2010-01-04', 400 from dual union all
  7  select date '2010-01-10', 800 from dual union all
  8  select date '2010-01-11', 800 from dual union all
  9  select date '2010-01-12', 800 from dual union all
 10  select date '2010-01-25', 500 from dual union all
 11  select date '2010-01-26', 500 from dual union all
 12  select date '2010-02-05', 600 from dual union all
 13  select date '2010-02-13', 700 from dual union all
 14  select date '2010-02-15', 700 from dual
 15  /

Table created.

This query uses MAX-DECODE as a standard pivot technique. If you are on version 11, you can also use the PIVOT operator. The below version will work on any version.

SQL> select nvl(max(decode(the_week,'01',cost)),0) "Week 1"
  2       , nvl(max(decode(the_week,'02',cost)),0) "Week 2"
  3       , nvl(max(decode(the_week,'03',cost)),0) "Week 3"
  4       , nvl(max(decode(the_week,'04',cost)),0) "Week 4"
  5       , nvl(max(decode(the_week,'05',cost)),0) "Week 5"
  6       , nvl(max(decode(the_week,'06',cost)),0) "Week 6"
  7       , nvl(max(decode(the_week,'07',cost)),0) "Week 7"
  8       , nvl(max(decode(the_week,'08',cost)),0) "Week 8"
  9       , nvl(max(decode(the_week,'09',cost)),0) "Week 9"
 10    from ( select to_char(the_date,'ww') the_week
 11                , sum(cost) cost
 12             from mytable
 13            where the_date between date '2010-01-01' and date '2010-02-28'
 14            group by to_char(the_date,'ww')
 15         )
 16  /

    Week 1     Week 2     Week 3     Week 4     Week 5     Week 6     Week 7     Week 8     Week 9
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
      1000       2400          0       1000          0        600       1400          0          0

1 row selected.

Regards, Rob.