Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: Query to display weekly data

    I have a table with following data:
    Sale_date Qty_sold
    ---------- ----------
    01-Apr-2013 10
    02-Apr-2013 0
    03-Apr-2013 0
    04-Apr-2013 0
    05-Apr-2013 12
    06-Apr-2013 0
    07-Apr-2013 0
    08-Apr-2013 15
    09-Apr-2013 23
    10-Apr-2013 0

    Now i would like to display the weekly qty_sold for a given date range.

    Say if date_range is 01-apr-2013 to 08-apr-2013 then it will consider first week from 01-apr-2013 to 07-apr-2013 and 2nd week from 08-apr-2013 to 08-apr-2013 and result will be as below

    Week tot_qty_sold
    ----- ----------
    1 22
    2 15

    If date_range is 02-apr-2013 to 10-apr-2013 then it will consider first week from 02-apr-2013 to 08-apr-2013 and 2nd week from 09-apr-2013 to 10-apr-2013 and result will be as below

    Week tot_qty_sold
    ------- -----------
    1 27
    2 23


  2. #2
    Join Date
    Jun 2004
    Liverpool, NY USA
    select trunc(sale_date,'W') week,sum(qty_sold)
    from sale_table
    group by trunc(sale_date,'W')
    order by trunc(sale_date,'W');

    This will not work with an arbitrary week
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Mar 2007
    The most difficult part is obtaining week number from SALE_DATE. Then, it is easy to aggregate by it, as Bill demonstrated.

    It may be achieved by using simple date arithmetics. Note, that you have to know the starting date of the DATE_RANGE; here, I chose TRUNC(SYSDATE). And, as you did not provide scripts for create your table including INSERT statements for sample data, I generated some of them in the WITH clause - you do not have to use it.
    with your_secret_table_name as ( select trunc(sysdate)+level-1 sale_date from dual connect by level <=8 )
    select sale_date, trunc( (sale_date-trunc(sysdate) )/ 7 )+1 week_no
    from your_secret_table_name;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts