# Thread: Query to display weekly data

1. Registered User
Join Date
Jan 2004
Posts
153

## 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

Regards,
JD

2. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
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

3. Registered User
Join Date
Mar 2007
Posts
629
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.
Code:
```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
•