Systems Engineering and RDBMS

Grouping on a weekly basis – Oracle

Posted by decipherinfosys on February 10, 2007

This is a requirement that I faced a few hours ago from one of our clients.  They have a table in which orders get tracked in the system and they needed to write a report in which they can group the data for their shipping date and see the output on a weekly basis i.e. how many orders were shipped in a particular week – they needed this information for the past 7 months.  In the actual report, this information was then combined with other information to show the drill-down into specific sales regions, specific sales people, territories, product categorization etc..  But, here is the simple query that can do the aggregation per week and show the data to the end user (this was for Oracle):

Name of the table changed to preserve client schema information

select trunc(ship_date_time,’w’) WEEK_OF, count(*)
from table_x
where stat_code = 90   /*the status input – shipped orders only*/
and ship_date_time > ? /*the criteria input*/
group by trunc(ship_date_time,’w’)

Sample abridged output:

——— ———-
01-JUN-06      22370
08-JUN-06      23648
15-JUN-06      20676
22-JUN-06      21083
01-JUL-06      18085
08-JUL-06      17871
15-JUL-06      23504
22-JUL-06      23465
29-JUL-06      13072
01-AUG-06      20685
08-AUG-06      19462

Sorry, the comment form is closed at this time.

%d bloggers like this: