Oracle – Creating SQL Query to Split Data by Weeks

oraclereporting

I am building a report to show order statistics by week within our company.

Here is the SQL query that I've created:

Select Sum(ORDER_TOTALS.ORDER_TOT_NET)  As "ORDER_TOT_NET",
       Sum(ORDER_TOTALS.ORDER_TOT_WGHT) As "ORDER_TOT_WGHT",
       Sum(ORDER_TOTALS.ORDER_TOT_QTY)  As "ORDER_TOT_QTY",    
       Sum(ORDER_TOTALS.ORDER_TOT_SQM)  As "ORDER_TOT_SQM"
From ORDER ORDER,
     ORDER_TOTALS ORDER_TOTALS
Where ORDER.ORDER_NO = ORDER_TOTALS.ORDER_NO
And   ORDER.DATE Between '14-DEC-15' And '18-DEC-15

As you can see this will show results for orders that were created last week however I do not understand now how to make it more advanced so that we can get each week of the current year for example.

Here are the current results:

ORDER_TOT_NET  |  ORDER_TOT_WGHT  |  ORDER_TOT_QTY  |  ORDER_TOT_SQM
1020304050.00  |  4567890         |  92328493       |  3923948

What I want is something around this idea:

          ORDER_TOT_NET  |  ORDER_TOT_WGHT  |  ORDER_TOT_QTY  |  ORDER_TOT_SQM
Week 1    1020304050.00  |  4567890         |  92328493       |  3923948
Week 2    1020304050.00  |  4567890         |  92328493       |  3923948
Week 3    1020304050.00  |  4567890         |  92328493       |  3923948

How could/should I create it so that I have these results but for Week 1 through to the current week? Is this something that I should build directly in to the SQL query?

I am using DBxtra reporting program.

Best Answer

To get the week number of a date use:

to_char(sysdate, 'WW')

You can group on this:

select   to_char(ORDER.DATE, 'WW') as WEEK
         Sum(ORDER_TOTALS.ORDER_TOT_NET)  As "ORDER_TOT_NET",
         Sum(ORDER_TOTALS.ORDER_TOT_WGHT) As "ORDER_TOT_WGHT",
         Sum(ORDER_TOTALS.ORDER_TOT_QTY)  As "ORDER_TOT_QTY",    
         Sum(ORDER_TOTALS.ORDER_TOT_SQM)  As "ORDER_TOT_SQM"
From     ORDER ORDER, ORDER_TOTALS ORDER_TOTALS
Where    ORDER.ORDER_NO = ORDER_TOTALS.ORDER_NO
group by to_char(ORDER.DATE, 'WW');

Instead of WW you can also use IW. The last one gives the ISO Week. Here the beginning or the end of the year might end up in the week of the previous/next year. Look for it in Wikipedia (https://en.wikipedia.org/wiki/ISO_8601) for more information on this.

Related Solutions

Oracle: How to query a Hierarchical table

FrusteratedWithFormsDesigner has the right direction(+1). Here is what I think you are looking for specifically.

CREATE OR REPLACE VIEW BuildingSubs AS
   SELECT connect_by_root location "Building", location "SubLocation"
   FROM some_table l
   START WITH l.Location IN 
      (
         SELECT location FROM
         (
         SELECT level MyLevel, location FROM some_table 
         START WITH parent IS NULL 
         CONNECT BY PRIOR location=parent
         )
         WHERE MyLevel=3   
      )
   CONNECT BY PRIOR l.location = l.parent;

select * from BuildingSubs; 

Building             SubLocation        
-------------------- --------------------
BLDG-01              BLDG-01              
BLDG-01              Basement             
BLDG-01              Room B-002           
BLDG-01              Floor-01             
BLDG-01              Room 1-001           
BLDG-02              BLDG-02              
BLDG-03              BLDG-03              
Grounds              Grounds

The view accomplishes all three goals. You can query it for a building to find everything it contains and you can query it for a sub-location to find what building it is in.

drop table some_table;
create table some_table (Location Varchar2(20), Parent Varchar2(20));

insert into some_table values ('Org. Name',NULL);
insert into some_table values ('MAINCAMPUS','Org. Name');
insert into some_table values ('BLDG-01','MAINCAMPUS');
insert into some_table values ('BLDG-02','MAINCAMPUS');
insert into some_table values ('BLDG-03','MAINCAMPUS');
insert into some_table values ('Grounds','MAINCAMPUS');
insert into some_table values ('Floor-01','BLDG-01');
insert into some_table values ('Basement','BLDG-01');
insert into some_table values ('Room B-002','Basement');
insert into some_table values ('Room 1-001','Floor-01');

If you don't want to count the building itself as one of the sub-locations you can wrap the existing query in one the eliminates entries in which the building and sublocation are the same.

Reporting Table Design

I would do something like this:

period_start_on date
period_duration interval
position        int -- position of this row in the year,
                    -- e.g. 1 for first Monday of Oct,
                    -- 2 for 2nd Monday, etc.
values ...
primary key(period_start_on, period_duration)
check(position >= 1 and position < 40)

Then if you want December only, you use WHERE period_duration = '1d' and period_start_on >= '2012-12-01' AND period_start_on < '2013-01-01'. If you want to compare two years, you JOIN using position:

SELECT y1.*, y2.*
FROM   the_table y1
  JOIN the_table y2 USING (position)
WHERE y1.period_start_on >= '2011-01-01' AND y1.period_start_on < '2012-01-01'
  AND y2.period_start_on >= '2012-01-01' AND y2.period_start_on < '2013-01-01'

Best Answer

Related Solutions

Oracle: How to query a Hierarchical table

Reporting Table Design

Related Question