0

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.

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.