Home » SQL & PL/SQL » SQL & PL/SQL » Sum Daily Total to Weekly in Oracle (Oracle 9i)
| Sum Daily Total to Weekly in Oracle [message #443850] |
Wed, 17 February 2010 11:55  |
emyk
Messages: 9
Registered: February 2010
Location: usa
|
Junior Member |
|
|
I need to sum the below daily total to weekly with the week starting day of Saturday Jan 3 2009 for the 52 weeks in 2009.
The below query provides me the weekly total with the week starting day MONDAY. But I need the week starting day to be SATURDAY instead of MONDAY.
select to_char(report_date, 'YYYYIW'), sum(total)
from report_table
where to_number(to_char(report_date,'YYYYIW')) >=
to_number(to_char(to_date( '&one_year_ago'),'YYYYIW'))
group by to_char(report_date, 'YYYYIW')
Here is a query I used to generate the Daily Sample Data:
SELECT DISTINCT A.PRODUCT, TO_CHAR(B.BEGIN_DT,'YYYY-MM-DD') as post_date,'RCSL', A.DEPTID, A.ACCOUNT, SUM( A.POSTED_TOTAL_AMT) as Amount_Posted
FROM A, B, C
AND B.BEGIN_DT BETWEEN TO_DATE('&StartDate','YYYY-MM-DD') AND TO_DATE('&EndDate','YYYY-MM-DD')
GROUP BY A.PRODUCT, TO_CHAR(B.BEGIN_DT,'YYYY-MM-DD'), A.DEPTID, A.ACCOUNT
For the below sample data I would need to add data starting 2010-01-02 - 2010-01-08 to get my weekly total.
7777,2010-01-01,RCSL,0170,331062,-85
7777,2010-01-02,RCSL,0170,331063,-190
7777,2010-01-03,RCSL,0170,341413,170.18
7777,2010-01-04,RCSL,0170,347103,-880
7777,2010-01-05,RCSL,0172,331050,-116
7777,2010-01-06,RCSL,0172,331053,-50
7777,2010-01-07,RCSL,0172,331061,-63
7777,2010-01-08,RCSL,0172,331061,-63
Any assistant or direction is greatly appreciated.
Thanks,
|
|
|
|
|
|
|
|
| Re: Sum Daily Total to Weekly in Oracle [message #443987 is a reply to message #443850] |
Thu, 18 February 2010 04:22  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You should just be able to doselect to_char(report_date+1, 'YYYYIW'), sum(total)
from report_table
where to_number(to_char(report_date,'YYYYIW')) >=
to_number(to_char(to_date( '&one_year_ago'),'YYYYIW'))
group by to_char(report_date+1, 'YYYYIW') |
|
|
|
| Re: Sum Daily Total to Weekly in Oracle [message #444265 is a reply to message #443850] |
Fri, 19 February 2010 16:56 |
emyk
Messages: 9
Registered: February 2010
Location: usa
|
Junior Member |
|
|
Thanks for your help! I am still struglling with the dates.
I am expecting this query to return my starting week (week 1) to be Jan 3rd 2009 but i am getting Dec.31 as week1.
Here is my query:
select TO_CHAR(B.BEGIN_DT+1,'YYYYIW') as post_date, A.product,b.begin_dt,SUM( A.POSTED_TOTAL_AMT) as Amount_Posted
from PS_LEDGER A, PS_CAL_DETP_TBL B, PS_BU_LED_GRP_TBL C
'I am using few days for test'
where to_number(to_char(B.BEGIN_DT+1,'YYYYIW')) >= to_number(to_char('200901'))
AND to_number(to_char(B.BEGIN_DT+1,'YYYYIW')) <= to_number(to_char('200903' ))
AND A.LEDGER = 'BUS_DAILY'
AND A.PRODUCT LIKE '9671E%'
AND A.FISCAL_YEAR = B.FISCAL_YEAR
AND A.ACCOUNTING_PERIOD = B.ACCOUNTING_PERIOD
AND B.CALENDAR_ID = C.CALENDAR_ID
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.LEDGER = C.LEDGER_GROUP
AND A.CURRENCY_CD = A.BASE_CURRENCY
group by TO_CHAR(B.BEGIN_DT+1,'YYYYIW'),A.product,b.begin_dt
Here is what i got when I run the above query.
POST_D PRODUC BEGIN_DT AMOUNT_POSTED
------ ------ --------- -------------
200901 9671E 31-DEC-08 7107.28
200901 9671E 01-JAN-09 3641.21
200901 9671E 02-JAN-09 3337.25
200901 9671E 03-JAN-09 3455.97
200902 9671E 04-JAN-09 2786.58
200902 9671E 05-JAN-09 2761.81
200902 9671E 06-JAN-09 2880.28
200902 9671E 07-JAN-09 3258.36
200902 9671E 08-JAN-09 3613.3
200902 9671E 09-JAN-09 6024.89
200902 9671E 10-JAN-09 7451.82
200903 9671E 11-JAN-09 7127.51
200903 9671E 12-JAN-09 5806.51
200903 9671E 13-JAN-09 5865.61
200903 9671E 14-JAN-09 3773.06
200903 9671E 15-JAN-09 6019.97
200903 9671E 16-JAN-09 6347
200903 9671E 17-JAN-09 5733.76
Expected Result is to start the week 1 at January 1st (200901):
POST_D PRODUC BEGIN_DT AMOUNT_POSTED
------ ------ --------- -------------
200901 9671E 01-JAN-09 7107.28
200901 9671E 01-JAN-09 3641.21
200901 9671E 01-JAN-09 3337.25
200901 9671E 01-JAN-09 3455.97
200901 9671E 01-JAN-09 2786.58
200901 9671E 01-JAN-09 2761.81
200901 9671E 01-JAN-09 2880.28
etc...
|
|
|
|
|
|
| Re: Sum Daily Total to Weekly in Oracle [message #444271 is a reply to message #443850] |
Fri, 19 February 2010 21:33 |
emyk
Messages: 9
Registered: February 2010
Location: usa
|
Junior Member |
|
|
Oracle Version
SQL> SELECT * from v$version
2 /
BANNER
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for HPUX: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
|
|
|
|
|
|
|
|
| Re: Sum Daily Total to Weekly in Oracle [message #444280 is a reply to message #444265] |
Sat, 20 February 2010 00:49 |
flyboy
Messages: 1831
Registered: November 2006
|
Senior Member |
|
|
emyk wrote on Fri, 19 February 2010 23:56I am expecting this query to return my starting week (week 1) to be Jan 3rd 2009 but i am getting Dec.31 as week1.
emyk wrote on Fri, 19 February 2010 23:56Expected Result is to start the week 1 at January 1st (200901)
These two statements are in contradiction; I have no idea which of them is correct (if any).
To your first expectation: you are using ISO week. It always starts on Monday. And as January 1st, 2009 is Thursday, the first ISO week of 2009 year starts on December 29th, 2008. If you are using another week numbering convention, you probably have to code its rules yourself.
To the second expectation: week of year ('WW' format) starts each year on January 1st, so it would fit them.
Just general advice: the WHERE condition makes the usage of index on BEGIN_DT impossible. Additionally, using to_number(to_char('200901'))to_number('200901') or 200901
Anyway, I would rewrite the WHERE condition to compare BEGIN_DT with the start/end of interval dates. Detecting them for ISO week is shown in this AskTom post (just follow the link; you might need to adjust it for different numbering week convention); detecting for week of year is trivial (difference from January 1st, each week has 7 days).
|
|
|
|
| Re: Sum Daily Total to Weekly in Oracle [message #444335 is a reply to message #444280] |
Sat, 20 February 2010 12:35 |
emyk
Messages: 9
Registered: February 2010
Location: usa
|
Junior Member |
|
|
The below query gives my the desired dates and week number. Now the challenge i have is how to display another corrosponding column "SUM(POSTED_TOTAL_AMT)" for the days. When adding the this column at the end of the first line i am getting invalid column.
select begin_dt, to_char(begin_dt-2,'ww')
from (select to_date('2-jan-2009')+rownum begin_dt
from ps_ledger)
where rownum < 30
group by to_char(begin_dt-2,'ww'),begin_dt
BEGIN_DT TO
--------- --
03-JAN-09 01
04-JAN-09 01
05-JAN-09 01
06-JAN-09 01
07-JAN-09 01
08-JAN-09 01
09-JAN-09 01
10-JAN-09 02
11-JAN-09 02
12-JAN-09 02
13-JAN-09 02
14-JAN-09 02
15-JAN-09 02
16-JAN-09 02
17-JAN-09 03
18-JAN-09 03
19-JAN-09 03
20-JAN-09 03
21-JAN-09 03
22-JAN-09 03
23-JAN-09 03
24-JAN-09 04
25-JAN-09 04
26-JAN-09 04
27-JAN-09 04
28-JAN-09 04
29-JAN-09 04
30-JAN-09 04
31-JAN-09 05
Expected Result is:
BEGIN_DT TO SUM(POSTED_TOTAL_AMT)
--------- -- --------------------
03-JAN-09 01
04-JAN-09 01
05-JAN-09 01
06-JAN-09 01
07-JAN-09 01
08-JAN-09 01
|
|
|
|
| Re: Sum Daily Total to Weekly in Oracle [message #444336 is a reply to message #444335] |
Sat, 20 February 2010 12:42 |
 |
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Post a working Test case: create table and insert statements along with the result you want with these data.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Regards
Michel
[Updated on: Sat, 20 February 2010 12:43] Report message to a moderator |
|
|
|
| Re: Sum Daily Total to Weekly in Oracle [message #444337 is a reply to message #444335] |
Sat, 20 February 2010 13:02 |
flyboy
Messages: 1831
Registered: November 2006
|
Senior Member |
|
|
I wonder if you are aware what you are doing. emyk wrote on Sat, 20 February 2010 19:35select begin_dt, to_char(begin_dt-2,'ww')
from (select to_date('2-jan-2009')+rownum begin_dt
from ps_ledger)
where rownum < 30
group by to_char(begin_dt-2,'ww'),begin_dt E.g. whatever was the reason for introducing subquery (it contains only BEGIN_DT column, which is the error reason; was it testing purpose?), why did you not replace it with PS_LEDGER? Or does PS_LEDGER not contain BEGIN_DT column? Sorry, I have no idea, what is the source data at all, as you did not post it.
I have no idea what that "magic" -2 does there and why does the first week start with Saturday, January 3rd 2009. Then, the year 2010 will start on Sunday, January 3rd. Anyway, as you did not post any rule for calculating it, I hope it will return expected results for other years as well.
|
|
|
|
| Re: Sum Daily Total to Weekly in Oracle [message #444339 is a reply to message #444337] |
Sat, 20 February 2010 13:13 |
emyk
Messages: 9
Registered: February 2010
Location: usa
|
Junior Member |
|
|
|
this is a one time report for 2009. The minus 2 majic number gave me the week starting day of saturday for the year 2009 which is January 3rd. You are correct, i have no idea how this query works, but one thing i know is that it gave me the desired days and week numbers.
|
|
|
|
|
|
|
|
| Re: Sum Daily Total to Weekly in Oracle [message #444343 is a reply to message #444340] |
Sat, 20 February 2010 13:27 |
flyboy
Messages: 1831
Registered: November 2006
|
Senior Member |
|
|
emyk wrote on Sat, 20 February 2010 20:13this is a one time report for 2009. The minus 2 majic number gave me the week starting day of saturday for the year 2009 which is January 3rd. You are correct, i have no idea how this query works, but one thing i know is that it gave me the desired days and week numbers.
emyk wrote on Sat, 20 February 2010 20:15PS_ledger has begin_dt, posted_total_amt column
Interestingly, you answer to my questions in reverse order. Maybe, the answer to the first one would give you the clue. Just for completeness, now you retrieve no column value from PS_LEDGER (as BEGIN_DT is generated).
|
|
|
|
Goto Forum:
Current Time: Fri Sep 30 10:38:44 CDT 2016
Total time taken to generate the page: 0.14515 seconds
|
] 
Members
Search
Help
Register
Login
Home
