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 Go to next message
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 #443853 is a reply to message #443850] Wed, 17 February 2010 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Many possible ways to solve this problem exist.
Below may get you started

http://www.orafaq.com/forum/m/443278/136107/#msg_443278
Re: Sum Daily Total to Weekly in Oracle [message #443857 is a reply to message #443850] Wed, 17 February 2010 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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.

Precise if Saturday is your national first week day or is it just the first week day for this report.

Regards
Michel

[Updated on: Sat, 20 February 2010 01:15]

Report message to a moderator

Re: Sum Daily Total to Weekly in Oracle [message #443987 is a reply to message #443850] Thu, 18 February 2010 04:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You should just be able to do
select 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 Go to previous messageGo to next message
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 #444266 is a reply to message #444265] Fri, 19 February 2010 17:23 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Sum Daily Total to Weekly in Oracle [message #444271 is a reply to message #443850] Fri, 19 February 2010 21:33 Go to previous messageGo to next message
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 #444272 is a reply to message #444271] Fri, 19 February 2010 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
Please realize that we don't have your table & we don't have your data.
Therefore we can't run, test & improve your posted SQL.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
Re: Sum Daily Total to Weekly in Oracle [message #444277 is a reply to message #444265] Sat, 20 February 2010 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 17 February 2010 19:13
Post a working Test case: create table and insert statements along with the result you want with these data.

Precise if Saturday is your national first week day or is it just the first week day for this report.

Regards
Michel

[Updated on: Sat, 20 February 2010 01:15]

Report message to a moderator

Re: Sum Daily Total to Weekly in Oracle [message #444280 is a reply to message #444265] Sat, 20 February 2010 00:49 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
emyk wrote on Fri, 19 February 2010 23:56
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.

emyk wrote on Fri, 19 February 2010 23:56
Expected 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'))
is needlessly complicated, as it is doing three conversions (one explicit in TO_CHAR). Why not simply
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I wonder if you are aware what you are doing. emyk wrote on Sat, 20 February 2010 19:35
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
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 Go to previous messageGo to next message
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 #444340 is a reply to message #444337] Sat, 20 February 2010 13:15 Go to previous messageGo to next message
emyk
Messages: 9
Registered: February 2010
Location: usa
Junior Member
PS_ledger has begin_dt, posted_total_amt column
Re: Sum Daily Total to Weekly in Oracle [message #444341 is a reply to message #444340] Sat, 20 February 2010 13:17 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
Please realize that we don't have your table & we don't have your data.
Therefore we can't run, test & improve your posted SQL.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
Re: Sum Daily Total to Weekly in Oracle [message #444343 is a reply to message #444340] Sat, 20 February 2010 13:27 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
emyk wrote on Sat, 20 February 2010 20:13
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.

emyk wrote on Sat, 20 February 2010 20:15
PS_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).
Previous Topic: update two table using for loop
Next Topic: Using OF Pl/sql table of Records
Goto Forum:
  


Current Time: Fri Dec 09 09:29:18 CST 2016

Total time taken to generate the page: 0.10965 seconds