|
-
displaying weeks
How to display weekly summary for a date range.
A week is from Sunday through Saturday
for example:
select week,count(orders) from orders where trunc(ordered_date)between '01-MAR-09' and '31-MAR-09'
group by week
week - orders
1-Mar-09--7-Mar-09 200
8-Mar-09 --14-Mar-09 300
15-Mar-09--21-Mar-09 400
22-Mar-09--28-Mar-09 900
29-Mar-09--31-Mar-09 300
i was thinking to do in this way SELECT (TO_CHAR(date1'd')-TO_CHAR(date2,'d'))/7 FROM dual; but if i do then last row may messup right where last wek has only 3 days
any help is much appreciated,thanks
Last edited by prodadmin; 04-06-2009 at 02:40 PM.
Reason: missed part of it
-
Why would you divide by 7 if you are counting orders?
Did you mean a weekly average?
Check AVG() function.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
no i am not doing weekly average
i am looking for to display the weeks date range and thier summary
-
try this
hi,
use this to find group of sundays to start,
then use where 'dates_in_question' between this_sun and next_sun
1 with dates
2 as
3 (
4 select next_day( trunc(sysdate,'year')-1, 'sun' )+(7*(rownum-1))
5 this_sun
6 from all_objects
7 where rownum <= 100 --(sysdate-trunc(sysdate,'year'))
8 )
9 select to_char(this_sun,'day') dow
10 ,this_sun ,lead(this_sun) over (order by this_sun) next_sun
11 from dates
12* order by this_sun
SQL> /
DOW THIS_SUN NEXT_SUN
--------- --------- ---------
sunday 04/JAN/09 11/JAN/09
sunday 11/JAN/09 18/JAN/09
sunday 18/JAN/09 25/JAN/09
sunday 25/JAN/09 01/FEB/09
sunday 01/FEB/09 08/FEB/09
sunday 08/FEB/09 15/FEB/09
sunday 15/FEB/09 22/FEB/09
sunday 22/FEB/09 01/MAR/09
sunday 01/MAR/09 08/MAR/09
sunday 08/MAR/09 15/MAR/09
sunday 15/MAR/09 22/MAR/09
DOW THIS_SUN NEXT_SUN
--------- --------- ---------
sunday 22/MAR/09 29/MAR/09
sunday 29/MAR/09 05/APR/09
sunday 05/APR/09 12/APR/09
sunday 12/APR/09 19/APR/09
sunday 19/APR/09 26/APR/09
sunday 26/APR/09 03/MAY/09
sunday 03/MAY/09 10/MAY/09
sunday 10/MAY/09 17/MAY/09
sunday 17/MAY/09 24/MAY/09
sunday 24/MAY/09 31/MAY/09
sunday 31/MAY/09 07/JUN/09
-
use this if you want sun->sat
1 with dates
2 as
3 (
4 select next_day( trunc(sysdate,'year')-1, 'sun' )+(7*(rownum-1))
5 this_sun
6 from all_objects
7 where rownum <= 100 --(sysdate-trunc(sysdate,'year'))
8 )
9 select to_char(this_sun,'day') dow
10 ,this_sun ,(lead(this_sun) over (order by this_sun))-1 end_week
11 from dates
12* order by this_sun
SQL> /
DOW THIS_SUN END_WEEK
--------- --------- ---------
sunday 04/JAN/09 10/JAN/09
sunday 11/JAN/09 17/JAN/09
sunday 18/JAN/09 24/JAN/09
sunday 25/JAN/09 31/JAN/09
sunday 01/FEB/09 07/FEB/09
sunday 08/FEB/09 14/FEB/09
sunday 15/FEB/09 21/FEB/09
sunday 22/FEB/09 28/FEB/09
sunday 01/MAR/09 07/MAR/09
sunday 08/MAR/09 14/MAR/09
sunday 15/MAR/09 21/MAR/09
DOW THIS_SUN END_WEEK
--------- --------- ---------
sunday 22/MAR/09 28/MAR/09
sunday 29/MAR/09 04/APR/09
sunday 05/APR/09 11/APR/09
sunday 12/APR/09 18/APR/09
sunday 19/APR/09 25/APR/09
sunday 26/APR/09 02/MAY/09
sunday 03/MAY/09 09/MAY/09
sunday 10/MAY/09 16/MAY/09
sunday 17/MAY/09 23/MAY/09
sunday 24/MAY/09 30/MAY/09
sunday 31/MAY/09 06/JUN/09
-
thank you ..let me try and will get back to you
Appreciate your help and time
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote