Stored Procedure for build a totals table in Oracle

Hello Experts Exchange
I need to write a stored procedure that will run on Oracle every Monday morning.  I am new to store procedure so not sure exactly how to write it.

I have attached a spreadsheet that has a table design in it.  The table design is a example of how I want the data to look like once the stored procedure has run, but I want it for a full year.

To build the table I need to use the data from the Retail_Weeks sheet where it has the month and the start week number and the end week number.

In the spreadsheet is a data sheet, this is example data. I need to group the data by SI_Week and get a Total of TTL_Income this need to go across the first line.  On the second line I need to group by the SI_Week and get a total of VAT_TOT.

As you can see on the table design I have January, February, March these need to be the total for the weeks that make up that month.

There is also a YTD this is a total for all the weeks in the year.

Can someone help me to build the SQL Query I need to build this table please?

Regards

SQLSearcher
Example-Data.xls
SQLSearcherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Instead of trying to solve the entire problem at one time, can you break this down into a smaller simplified test case?

I really don't have the time to try and figure out all the columns and all data in addition to the specific requirements.

If you can provide a few rows of data and expected results (not just the definition of the results but the actual numbers based on the raw data you provide), I might be able to spend a little time on this.

It looks like you want to do a simple PIVOT with your data.

I'm also not sure what the stored procedure is going to do.
MikeOM_DBACommented:
And who is going to read this very long two-liner 67 column unreadable spreadsheet?
Terrible design, recommend you come up with a better, simpler alternative.
Mark GeerlingsDatabase AdministratorCommented:
Using a stored procedure to create a table dynamically is usually *NOT* the best way in Oracle to solve a business problem.  By contrast, in SQL Server, this approach may be a good idea.

As the others have suggested, please try to describe the business problem you have in detail, or in small steps.  Then, allow us to recommend an approach that can work well in Oracle to solve the problem.  This may involve the use of one or more of the following: views, materialized views, global temporary tables, PL\SQL procedures, etc.  But, until we have a clear understanding of where the data is now (including the tables and indexes, assuming the data is in Oracle somewhere) and of exactly what you need to produce and how often (hourly, daily, weekly, monthly, etc.) we can't give you good recommendations.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SQLSearcherAuthor Commented:
Hello Experts
What I am trying  to achieve is to recreate a report that is currently created by Fox Pro, as its no longer supported we are moving to SSRS to display this report, but because of how complicated it is, it is not possible to do in SSRS.  It was suggested to me on another EE question that a stored procedure was the way to go, so I opened this question.  My plan is to create the table and then just display the whole table in SSRS.

The data is in Oracle, but I can develop a solution in SSIS if it makes it better to resolve the problem.

If we can work on the first problem of how to create a dynamic table for the weeks and the totals, using the Retail_Weeks data.  I can then open further questions for the next problems.

Regards

SQLSearcher
Mark GeerlingsDatabase AdministratorCommented:
For developing a report on data in Oracle, it is usually *NOT* necessary (nor recommended) to create a table dynamically.  I'm not sure what you mean exactly by "dynamic table".  From an Oracle perspective, that looks like a contradiction, since Oracle table definitions are persistent, not dynamic.  

Oracle does support "global temporary tables".  These are *NOT* dynamically created in programs though.  These usually get created by a DBA, and the table definition is persistent after that.  They are usually empty of contents, but they can be filled dynamically by a stored procedure to help with some complex reports.

Two Oracle objects that are somewhat dynamic are views and materialized views.  Either or both of these can also help with reporting.

Please try to describe exactly what data you have to work with, and what are the results you want.  Then allow us to recommend the Oracle options or features that are likely to be the most helpful for your report.
SQLSearcherAuthor Commented:
Hello Experts
So dynamic table, what I want is to create a table based on the Retail_Weeks data that is in the spreadsheet attached.  Every year this data will change so I want the table to change. So for example the table will be called Reporting2014 for data in 2014, the next table will be called Reporting2015 for data 2015 and the next table will be called Reporting2016 for next years data.  Retail_Weeks currently has just the weeks structure for this year.

So if we look at the Retail_Weeks first line.
RW_Month      No_Weeks      Week_Start      Week_End
1                             5                       1                      5

So for the table Reporting2015 I want it to create the table.
YTD,WK1,WK2,WK3,WK4,WK5,January..........

I want it to process each line from Retail_Weeks to then build the Reporting2015 table.

---------------------------------------------------------------------------------------------------------------------------------------
Step 2 is to query the data table. Also in the spreadsheet attached.

For example;
Select SI_Week, Sum(TTL_Income)
from Data
Group by SI_Week

This will return for example.
1, 611.45
2, 2913.18..........

----------------------------------------------------------------------------------------------------------------------------------
Step3
Once I have the data from my query I want the data to go horizontally accross the Reporting2015 table.
YTD, WK1, WK2, WK3, WK4, WK5, January
3524.63, 611.45, 2913.18.,.,.,3524.63

Regards

SQLSearcher
Mark GeerlingsDatabase AdministratorCommented:
Where does the data come from (or where is it located) before it gets into the spreadsheet that you attached?

From what you described, it still sounds to me like a "global temporary table" (plus a PL\SQL procedure to populate it dynamically) or a view or possibly a materialized view could be a good option.  But, until we learn where the persistent data is that you want to report on, we don't know which approach will work best.

You indicated again that you may want a table created to hold data for each year.  That is possible, but that may or may not be your best option.  If you need to run lots of reports based on all (or maybe parts of) that data, then creating a table for each year may be a good option.  But, if you simply need to produce a single report, then that is most likely not your best option in Oracle.
slightwv (䄆 Netminder) Commented:
I agree with the previous comments.  I'm not seeing the need to a table.  If the data amounts are so large they cannot be computed at run time I would look at materialized views.

It still looks to me like a pivot table.

Instead of trying to explain what the data needs to do, please show us.

Create a simplified test case with sample data and expected results.  We need something to set up a test case.  then we can provide actual working examples.

For example:
I have tableA with
WK   VAL
1    2
2    3
4    5

Open in new window


from that I need the results to look like:
WK1   WK2   WK3   TOTAL
2     3     5     10

Open in new window



Then we can set up a model and actually post code that produces your results.
SQLSearcherAuthor Commented:
Hello Experts
What I want is very much like a pivot table with total values for each month.

The example data I have given you is only a small set of data I need to query, I need to query several sets of data to get the report I require, and the data is not in just one table it is in multiple tables.

Slightwv the examples you are asking for is that not in my last comment?

Regards

SQLSearcher
Mark GeerlingsDatabase AdministratorCommented:
We believe you when you say this: "I need to query several sets of data to get the report I require, and the data is not in just one table it is in multiple tables."

In Oracle though, that does not automatically mean that the best solution to this reporting challenge is to create a permanent table to contain summaries or sub-sets of data that already exists elsewhere.  That is only one of at least four possible ways of solving this problem.  I mentioned three other options earlier:
1. a view (or views)
2. a materialized view (or materialized views)
3. a global temporary table (and a PL\SQL procedure to populate it)

Until we know what your data sources look like (table structure, indexes, numbers of records, are they all in the same database, etc.) and the reporting requirements (once per minute/hour/day/week/month/qtr, etc.) it is very difficult for us to recommend the best option for you.
SQLSearcherAuthor Commented:
Hello Markgeer
I would much prefer a table to hold my data other than your three other options, as I have said I am using SQL Server Reporting Service to display my report I have already several reports in SSRS for the Users.  I am going to have a table for each year the data can be created for and have SSRS dynamically pick the data from the correct table.  I want to get the data as quickly as possible so to have the data already computed in a table will give the users the best performance when viewing the report.

The data for the current year needs to change once a week on a Monday.

Tables are all in the same database.

Count of records in Site_Income table is 563557.

I have attached table structure to Site_Income.

Regards

SQLSearcher
SITE_INCOME_Table_Design.xls
slightwv (䄆 Netminder) Commented:
>>Slightwv the examples you are asking for is that not in my last comment?

I really don't see an example that I need to complete the test case I was looking for.

Here is what I mocked up based on the little bit of data you did provide.

I hope it gives you an idea of how to do what you want.  If not, please add to the example.

/*
drop table tab1 purge;
create table tab1( si_week number, ttl_income number);

insert into tab1 values(1, 611.45);
insert into tab1 values(2, 2913.18);
commit;

*/

select wk1, wk2, wk1+wk2 two_week_total, wk3, wk4 from (
	select si_week, ttl_income
	from tab1
)
pivot 
(
   sum(ttl_income)
   for si_week in (1 as wk1,2 as wk2,3 as wk3,4 as wk4,5 as wk5,6 as wk6)
)
/

Open in new window


>>I am going to have a table for each year the data can be created

Seems like a bad design to me.  But so does storing data in Oracle to create reports in SQL Server but it is the path you seem to be sold on.

At a minimum, I wouldn't have a table for each year.  One table for ALL years should be fine.

A Materialized View (MV) would also probably be better than a table that you need to manually update.  A materialized view is a query that is stored as a table.  You can set the refresh to once a week if you want.

With the MV you don't need the procedure.  Just get the query working and let the MV do the rest.
SQLSearcherAuthor Commented:
Hello Slightwv
>>At a minimum, I wouldn't have a table for each year.  One table for ALL years should be fine.
As the total columns move each year one table for the data will not work.

Thank you for the code but I still need to build the total columns dynamically, I don't want to write new code year on year.

The code you have given me.
select wk1, wk2, wk1+wk2 two_week_total, wk3, wk4 from (
	select si_week, ttl_income
	from tab1

Open in new window



How do I build this up dynamically.

How do I get a total column using the retail_weeks table?

RW_Month      No_Weeks      Week_Start      Week_End
1                               5                        1                     5
2                               4                        6                     9
3                               5                        10                     14

So with the table above I need a total at column 6 for weeks 1 to 5, I needs a total at column 11 for weeks 6 to 9, and I need a total at column 17 for weeks 10 to 14 and so on.

Regards

SQLSearcher
slightwv (䄆 Netminder) Commented:
You can keep trying to explain it but I'm still not understanding the actual requirements.

I posted code based on the sample data and results I understood.  I don't understand the rest of the the requirements.

I don't understand the rest of your requirements.  You can keep trying to explain it to me but I feel that would be wasted effort.

Please post more sample data and expected results.

If you don't post it I'm afraid I'll not be able to assist further.
Mark GeerlingsDatabase AdministratorCommented:
I agree with slightwv that the requirements that you have provided are not clear, especially the need for a total (or sub-total) for various groups of weeks.  Do those weekly groups ever change depending on what week of the month the current date is in?
SQLSearcherAuthor Commented:
Hello Experts
Can we created a SQL script with several loops in it? But I don't know how to write it.

The first loop would be for each month of the year.

So for example;
For Month_Number 1.. 12
Loop
Select Week_Start, Week_End
From Retail_Weeks
where RW_Month = Month_Number

Week_Number = Week_Start

For Week_Number to Week_End
Loop
Select SI_Week, Sum(TTL_Income)
Into Temp_table1
 from Data
Where SI_Week = Week_Number

Week_Number = Week_Number + 1
End Week_Number Loop

Select Month_Number, Sum(TTL_Income)
Into Temp_table1
 from Data
Where SI_Week between Week_Start and Week_End

End Month_Number Loop

Open in new window


Can we do something like that?

Regards

SQLSearcher
Mark GeerlingsDatabase AdministratorCommented:
"Can we create a SQL script with several loops in it?"
Yes.

Is that the best way to solve this problem (or any business problem)?
Maybe, but not always

For example, in Oracle it usually is not necessary to use two loops, one for months and a nested one for weeks in each month.  Usually it is possible to get that job done with just one loop at the detail level (weeks) and the month for each week can also be included in the output.
slightwv (䄆 Netminder) Commented:
What is the reason you continue to not post sample data and expected results?

We could have probably provided an answer a long time ago if you just posted a simple test case.
SQLSearcherAuthor Commented:
Hello
This is a sample of the expected results I would like;

 WK1, WK2, WK3, WK4, WK5, January,WK6,WK7,WK8,WK9,February
 611.45, 2913.18, 2548.32, 2830.14, 2870.58, 11773.67, 3125.33, 3292.45, 3781.63, 2966.39, 13165.80

Can you give me the script that matches the loops I am trying to do in my post from 2015-12-18 at 12:18:52, as I have to run multiple queries of data.

Regards

SQLSearcher
slightwv (䄆 Netminder) Commented:
>>This is a sample of the expected results I would like;

What is the actual RAW data that produces those results?

>>as I have to run multiple queries of data.

Who says?
SQLSearcherAuthor Commented:
Hello Slightwv
The raw data that produces the results is in the spreadsheet under a worksheet called data, that I attached on my original question comment.

The data worksheet is just one of multiple queries I will have to run and convert into the new table structure.  I thought I would start with the beginning in this question and once I had that I could move on to the other queries later.

Regards

SQLSearcher
Mark GeerlingsDatabase AdministratorCommented:
This is the part of your process that slightwv and I question the most: "convert into the new table structure".  Since you are using an Oracle database for at least part of this process, and since slightwv and I have extensive Oracle experience, we tend to approach a business problem like this by first thinking: "What is the simplest and most efficient way this can be done in Oracle?"

Creating a new table is usually *NOT* the simplest and most-efficient way to do a reporting task like this in Oracle.  In some cases, a "Data Warehouse" or a reporting table can be the simplest or best approach to the problem.  But, we don't automatically assume that is the best approach.

That is why we've been asking for what the raw or base data looks like, and what you want the reports to look like each year.
slightwv (䄆 Netminder) Commented:
Here is my test case based on your data from the data tab.

It produces your expected results.  It is the same PIVOT query I posted above.

/*
drop table myData purge;
create table myData(SI_Week number, TTL_Income number);
insert into mydata values(1,240.35);
insert into mydata values(1,192.99);
insert into mydata values(1,178.11);
insert into mydata values(2,216.79);
insert into mydata values(2,173.13);
insert into mydata values(2,216.16);
insert into mydata values(2,236.21);
insert into mydata values(2,207.95);
insert into mydata values(2,224.45);
insert into mydata values(2,52.22);
insert into mydata values(2,60.75);
insert into mydata values(2,100.75);
insert into mydata values(2,140.84);
insert into mydata values(2,124.92);
insert into mydata values(2,176.03);
insert into mydata values(2,123.67);
insert into mydata values(2,96.27);
insert into mydata values(2,130.89);
insert into mydata values(2,162.94);
insert into mydata values(2,225.27);
insert into mydata values(2,243.94);
insert into mydata values(3,131.58);
insert into mydata values(3,148.31);
insert into mydata values(3,222.72);
insert into mydata values(3,131.33);
insert into mydata values(3,235.81);
insert into mydata values(3,261.47);
insert into mydata values(3,34.01);
insert into mydata values(3,40.99);
insert into mydata values(3,68.22);
insert into mydata values(3,64.31);
insert into mydata values(3,259.91);
insert into mydata values(3,179.24);
insert into mydata values(3,79.83);
insert into mydata values(3,179.93);
insert into mydata values(3,96.75);
insert into mydata values(3,182.12);
insert into mydata values(3,231.79);
insert into mydata values(4,112.09);
insert into mydata values(4,144.62);
insert into mydata values(4,157.75);
insert into mydata values(4,257.72);
insert into mydata values(4,174.05);
insert into mydata values(4,240.25);
insert into mydata values(4,87.63);
insert into mydata values(4,154.38);
insert into mydata values(4,110.94);
insert into mydata values(4,100.15);
insert into mydata values(4,119.68);
insert into mydata values(4,242.64);
insert into mydata values(4,154.57);
insert into mydata values(4,95.84);
insert into mydata values(4,87.56);
insert into mydata values(4,143.01);
insert into mydata values(4,187.84);
insert into mydata values(4,259.42);
insert into mydata values(5,102.88);
insert into mydata values(5,207.33);
insert into mydata values(5,253.87);
insert into mydata values(5,183.93);
insert into mydata values(5,158.45);
insert into mydata values(5,101.56);
insert into mydata values(5,259.4);
insert into mydata values(5,173.98);
insert into mydata values(5,200.49);
insert into mydata values(5,262.55);
insert into mydata values(5,114.83);
insert into mydata values(5,117.28);
insert into mydata values(5,166.48);
insert into mydata values(5,389.59);
insert into mydata values(5,177.96);
insert into mydata values(6,147.86);
insert into mydata values(6,130.06);
insert into mydata values(6,137.52);
insert into mydata values(6,191.75);
insert into mydata values(6,242.62);
insert into mydata values(6,237.37);
insert into mydata values(6,194.3);
insert into mydata values(6,336.51);
insert into mydata values(6,174.25);
insert into mydata values(6,222.45);
insert into mydata values(6,249.38);
insert into mydata values(6,108.33);
insert into mydata values(6,117.99);
insert into mydata values(6,98.98);
insert into mydata values(6,174.3);
insert into mydata values(6,145.85);
insert into mydata values(6,215.81);
insert into mydata values(7,269.25);
insert into mydata values(7,169.49);
insert into mydata values(7,129.77);
insert into mydata values(7,246.31);
insert into mydata values(7,270.44);
insert into mydata values(7,150.02);
insert into mydata values(7,150.53);
insert into mydata values(7,154.6);
insert into mydata values(7,170.87);
insert into mydata values(7,239.25);
insert into mydata values(7,310.66);
insert into mydata values(7,96.58);
insert into mydata values(7,171.9);
insert into mydata values(7,186.88);
insert into mydata values(7,143.51);
insert into mydata values(7,153.36);
insert into mydata values(7,279.03);
insert into mydata values(8,147.05);
insert into mydata values(8,216.05);
insert into mydata values(8,185.63);
insert into mydata values(8,554.34);
insert into mydata values(8,344.92);
insert into mydata values(8,143.72);
insert into mydata values(8,162.13);
insert into mydata values(8,195.65);
insert into mydata values(8,125.58);
insert into mydata values(8,174.81);
insert into mydata values(8,337.2);
insert into mydata values(8,133.73);
insert into mydata values(8,361.68);
insert into mydata values(8,236.69);
insert into mydata values(8,170.8);
insert into mydata values(8,291.65);
insert into mydata values(9,157.7);
insert into mydata values(9,138.14);
insert into mydata values(9,178.21);
insert into mydata values(9,400.25);
insert into mydata values(9,211.97);
insert into mydata values(9,31.19);
insert into mydata values(9,119.73);
insert into mydata values(9,120.14);
insert into mydata values(9,304.45);
insert into mydata values(9,165.14);
insert into mydata values(9,125.54);
insert into mydata values(9,133.58);
insert into mydata values(9,148.38);
insert into mydata values(9,139.99);
insert into mydata values(9,239.21);
insert into mydata values(9,352.77);
insert into mydata values(10,116.7);
insert into mydata values(10,196.39);
insert into mydata values(10,209.44);
insert into mydata values(10,170.83);
insert into mydata values(10,276.97);
insert into mydata values(10,250.14);
insert into mydata values(10,94.67);
insert into mydata values(10,96.71);
insert into mydata values(10,133.08);
insert into mydata values(10,139.27);
insert into mydata values(10,147.3);
insert into mydata values(10,201.06);
insert into mydata values(10,218.24);
insert into mydata values(10,120.66);
insert into mydata values(10,173.08);
insert into mydata values(10,215.58);
insert into mydata values(10,184.95);
insert into mydata values(10,262.02);
insert into mydata values(11,102.54);
insert into mydata values(11,147.86);
insert into mydata values(11,129.49);
insert into mydata values(11,165.03);
insert into mydata values(11,172.08);
insert into mydata values(11,316.57);
insert into mydata values(11,106.84);
insert into mydata values(11,94.96);
insert into mydata values(11,125.93);
insert into mydata values(11,171.51);
insert into mydata values(11,224.12);
insert into mydata values(11,251.11);
insert into mydata values(11,223.46);
insert into mydata values(11,416.29);
insert into mydata values(11,126.43);
insert into mydata values(11,179.05);
insert into mydata values(11,359.88);
insert into mydata values(12,118.68);
insert into mydata values(12,229.95);
insert into mydata values(12,142.36);
insert into mydata values(12,194.66);
insert into mydata values(12,248.79);
insert into mydata values(12,330.56);
insert into mydata values(12,167.13);
insert into mydata values(12,158.77);
insert into mydata values(12,196.88);
insert into mydata values(12,230.45);
insert into mydata values(12,233.33);
insert into mydata values(12,244.91);
insert into mydata values(12,96.21);
insert into mydata values(12,190.86);
insert into mydata values(12,215.1);
insert into mydata values(12,148.17);
insert into mydata values(12,221.67);
insert into mydata values(12,298.23);
insert into mydata values(13,155.93);
insert into mydata values(13,153.16);
insert into mydata values(13,144.27);
insert into mydata values(13,167);
insert into mydata values(13,180.09);
insert into mydata values(13,231.09);
insert into mydata values(13,83.87);
insert into mydata values(13,128.77);
insert into mydata values(13,159.88);
insert into mydata values(13,172.93);
insert into mydata values(13,164.91);
insert into mydata values(13,239.55);
insert into mydata values(13,119.06);
insert into mydata values(13,291.52);
insert into mydata values(13,103.87);
insert into mydata values(13,172.31);
insert into mydata values(13,282.34);
insert into mydata values(13,122.02);
insert into mydata values(14,71.79);
insert into mydata values(14,158.15);
insert into mydata values(14,135.69);
insert into mydata values(14,171.49);
insert into mydata values(14,197.72);
insert into mydata values(14,258.56);
insert into mydata values(14,65.78);
insert into mydata values(14,72.47);
insert into mydata values(14,121.26);
insert into mydata values(14,165.21);
insert into mydata values(14,166.99);
insert into mydata values(14,324.45);
insert into mydata values(14,110.49);
insert into mydata values(14,129.69);
insert into mydata values(14,103.33);
insert into mydata values(14,369.72);
insert into mydata values(14,191.88);
insert into mydata values(15,143.89);
insert into mydata values(15,173.33);
insert into mydata values(15,134.29);
insert into mydata values(15,211.62);
insert into mydata values(15,283.32);
insert into mydata values(15,179.92);
insert into mydata values(15,156.69);
insert into mydata values(15,132.86);
insert into mydata values(15,218.85);
insert into mydata values(15,246);
insert into mydata values(15,228.07);
insert into mydata values(15,272.94);
insert into mydata values(15,94.73);
insert into mydata values(15,162.38);
insert into mydata values(15,149.1);
insert into mydata values(15,182.21);
insert into mydata values(15,198.17);
insert into mydata values(15,330.6);
insert into mydata values(16,150.53);
insert into mydata values(16,187.94);
insert into mydata values(16,178.22);
insert into mydata values(16,161.56);
insert into mydata values(16,225.94);
insert into mydata values(16,328.65);
insert into mydata values(16,107.86);
insert into mydata values(16,139.23);
insert into mydata values(16,114.25);
insert into mydata values(16,172.99);
insert into mydata values(16,227);
insert into mydata values(16,338.94);
insert into mydata values(16,195.43);
insert into mydata values(16,127.56);
insert into mydata values(16,399.33);
insert into mydata values(16,181.52);
insert into mydata values(16,249.25);
insert into mydata values(17,138.19);
insert into mydata values(17,185.92);
insert into mydata values(17,149.78);
insert into mydata values(17,177.91);
insert into mydata values(17,323.87);
insert into mydata values(17,228.41);
insert into mydata values(17,146.85);
insert into mydata values(17,134.63);
insert into mydata values(17,505.46);
insert into mydata values(17,293.65);
insert into mydata values(17,279.75);
insert into mydata values(17,40.92);
insert into mydata values(17,94.73);
insert into mydata values(17,122.01);
insert into mydata values(17,158.09);
insert into mydata values(17,218.97);
insert into mydata values(17,166.04);
insert into mydata values(17,188.28);
insert into mydata values(18,227.34);
insert into mydata values(18,187.32);
insert into mydata values(18,240.61);
insert into mydata values(18,179.52);
insert into mydata values(18,168.78);
insert into mydata values(18,345.53);
insert into mydata values(18,109.7);
insert into mydata values(18,122.32);
insert into mydata values(18,128.45);
insert into mydata values(18,138.56);
insert into mydata values(18,102.9);
insert into mydata values(18,269.71);
insert into mydata values(18,14.95);
insert into mydata values(18,128.24);
insert into mydata values(18,151.71);
insert into mydata values(18,149.31);
insert into mydata values(18,175.67);
insert into mydata values(18,204.61);
insert into mydata values(18,257.63);
insert into mydata values(19,179.49);
insert into mydata values(19,167.14);
insert into mydata values(19,157.23);
insert into mydata values(19,198.82);
insert into mydata values(19,241.92);
insert into mydata values(19,294.62);
insert into mydata values(19,44.4);
insert into mydata values(19,117.58);
insert into mydata values(19,157.59);
insert into mydata values(19,209.37);
insert into mydata values(19,152.07);
insert into mydata values(19,207.35);
insert into mydata values(19,272.22);
insert into mydata values(19,29.62);
insert into mydata values(19,85.23);
insert into mydata values(19,96.49);
insert into mydata values(19,143.43);
insert into mydata values(19,174.14);
insert into mydata values(19,202.57);
insert into mydata values(19,251.27);
insert into mydata values(20,164.11);
insert into mydata values(20,92.33);
insert into mydata values(20,195.17);
insert into mydata values(20,193.14);
insert into mydata values(20,361.1);
insert into mydata values(20,50.21);
insert into mydata values(20,86.98);
insert into mydata values(20,105.51);
insert into mydata values(20,136.17);
insert into mydata values(20,143.45);
insert into mydata values(20,170.26);
insert into mydata values(20,160.36);
insert into mydata values(20,36.11);
insert into mydata values(20,334.32);
insert into mydata values(20,132.75);
insert into mydata values(20,114.43);
insert into mydata values(20,195.89);
insert into mydata values(21,127.99);
insert into mydata values(21,123.67);
insert into mydata values(21,133.01);
insert into mydata values(21,343.3);
insert into mydata values(21,183.65);
insert into mydata values(21,55.94);
insert into mydata values(21,94.01);
insert into mydata values(21,142.5);
insert into mydata values(21,195.26);
insert into mydata values(21,200.39);
insert into mydata values(21,212);
insert into mydata values(21,302.04);
insert into mydata values(21,211.93);
insert into mydata values(21,106.17);
insert into mydata values(21,191.69);
insert into mydata values(21,169.63);
insert into mydata values(21,389.46);
insert into mydata values(22,166.16);
insert into mydata values(22,180.16);
insert into mydata values(22,141);
insert into mydata values(22,185.43);
insert into mydata values(22,226.89);
insert into mydata values(22,177.39);
insert into mydata values(22,55);
insert into mydata values(22,175);
insert into mydata values(22,185.13);
insert into mydata values(22,226.98);
insert into mydata values(22,408.68);
insert into mydata values(22,256.86);
insert into mydata values(22,94.13);
insert into mydata values(22,207.76);
insert into mydata values(22,158.18);
insert into mydata values(22,129.79);
insert into mydata values(22,190.94);
insert into mydata values(23,134.88);
insert into mydata values(23,153.96);
insert into mydata values(23,452.31);
insert into mydata values(23,188.34);
insert into mydata values(23,230.76);
insert into mydata values(23,71.05);
insert into mydata values(23,106.16);
insert into mydata values(23,125.73);
insert into mydata values(23,229.94);
insert into mydata values(23,164.08);
insert into mydata values(23,152.7);
insert into mydata values(23,127.43);
insert into mydata values(23,123.1);
insert into mydata values(23,135.35);
insert into mydata values(23,227.93);
insert into mydata values(23,252.94);
insert into mydata values(24,104.99);
insert into mydata values(24,133.55);
insert into mydata values(24,153.22);
insert into mydata values(24,176.22);
insert into mydata values(24,161.73);
insert into mydata values(24,206.93);
insert into mydata values(24,102.06);
insert into mydata values(24,187.38);
insert into mydata values(24,210.56);
insert into mydata values(24,507.31);
insert into mydata values(24,188.04);
insert into mydata values(24,314.32);
insert into mydata values(24,286.38);
insert into mydata values(24,135.35);
insert into mydata values(24,141.7);
insert into mydata values(24,162.95);
insert into mydata values(24,204.28);
insert into mydata values(25,114.03);
insert into mydata values(25,288.79);
insert into mydata values(25,154.54);
insert into mydata values(25,128.51);
insert into mydata values(25,175.58);
insert into mydata values(25,89.81);
insert into mydata values(25,119.65);
insert into mydata values(25,138.93);
insert into mydata values(25,167.78);
insert into mydata values(25,158.61);
insert into mydata values(25,199.13);
insert into mydata values(25,215.16);
insert into mydata values(25,84.52);
insert into mydata values(25,98.89);
insert into mydata values(25,133.82);
insert into mydata values(25,168.52);
insert into mydata values(25,163.13);
insert into mydata values(25,262.23);
insert into mydata values(26,148.07);
insert into mydata values(26,129.29);
insert into mydata values(26,179.39);
insert into mydata values(26,167.49);
insert into mydata values(26,210.71);
insert into mydata values(26,199.76);
insert into mydata values(26,122.34);
insert into mydata values(26,128.37);
insert into mydata values(26,181.89);
insert into mydata values(26,190.47);
insert into mydata values(26,326.93);
insert into mydata values(26,248.38);
insert into mydata values(26,429.5);
insert into mydata values(26,213.8);
insert into mydata values(26,160.87);
insert into mydata values(26,119.44);
insert into mydata values(26,263.87);
insert into mydata values(26,196.22);
insert into mydata values(27,96.65);
insert into mydata values(27,157.45);
insert into mydata values(27,176.41);
insert into mydata values(27,178.12);
insert into mydata values(27,197.38);
insert into mydata values(27,194.29);
insert into mydata values(27,68.77);
insert into mydata values(27,120.71);
insert into mydata values(27,311.89);
insert into mydata values(27,174.38);
insert into mydata values(27,223.52);
insert into mydata values(27,241.79);
insert into mydata values(27,106.64);
insert into mydata values(27,258.37);
insert into mydata values(27,138.39);
insert into mydata values(27,158.47);
insert into mydata values(27,128.54);
insert into mydata values(28,135.77);
insert into mydata values(28,118.08);
insert into mydata values(28,140.32);
insert into mydata values(28,130.57);
insert into mydata values(28,132.86);
insert into mydata values(28,162.48);
insert into mydata values(28,101.67);
insert into mydata values(28,118.48);
insert into mydata values(28,154.53);
insert into mydata values(28,104.08);
insert into mydata values(28,195.01);
insert into mydata values(28,216.27);
insert into mydata values(28,316.34);
insert into mydata values(28,156.23);
insert into mydata values(28,112.93);
insert into mydata values(28,176.39);
insert into mydata values(28,154.77);
insert into mydata values(28,166.45);
insert into mydata values(28,162.51);
insert into mydata values(29,110.61);
insert into mydata values(29,291.04);
insert into mydata values(29,151.99);
insert into mydata values(29,204.67);
insert into mydata values(29,201.31);
insert into mydata values(29,128.14);
insert into mydata values(29,129.53);
insert into mydata values(29,133.85);
insert into mydata values(29,179.8);
insert into mydata values(29,209.81);
insert into mydata values(29,222.56);
insert into mydata values(29,112.73);
insert into mydata values(29,102.28);
insert into mydata values(29,86.08);
insert into mydata values(29,120.87);
insert into mydata values(29,156.65);
insert into mydata values(29,121.63);
insert into mydata values(30,111.14);
insert into mydata values(30,242.7);
insert into mydata values(30,452.39);
insert into mydata values(30,181.9);
insert into mydata values(30,108.7);
insert into mydata values(30,147.27);
insert into mydata values(30,164.26);
insert into mydata values(30,153.8);
insert into mydata values(30,161.48);
insert into mydata values(30,213.48);
insert into mydata values(31,113.62);
insert into mydata values(31,136.77);
insert into mydata values(31,135.89);
insert into mydata values(31,137.86);
insert into mydata values(31,385.65);
insert into mydata values(31,121.98);
insert into mydata values(31,149.49);
insert into mydata values(31,195.75);
insert into mydata values(31,168.81);
insert into mydata values(31,175.11);
insert into mydata values(31,198.61);
insert into mydata values(32,151.14);
insert into mydata values(32,184.13);
insert into mydata values(32,231.57);
insert into mydata values(32,227.12);
insert into mydata values(32,269.5);
insert into mydata values(32,286.57);
insert into mydata values(32,132.02);
insert into mydata values(32,305.56);
insert into mydata values(32,144.56);
insert into mydata values(32,173.12);
insert into mydata values(32,214.8);
insert into mydata values(33,280.64);
insert into mydata values(33,180.65);
insert into mydata values(33,378.98);
insert into mydata values(33,209.48);
insert into mydata values(33,377.18);
insert into mydata values(33,91.14);
insert into mydata values(33,134.95);
insert into mydata values(33,104.97);
insert into mydata values(33,106.65);
insert into mydata values(33,161.41);
insert into mydata values(33,216.62);
insert into mydata values(34,385.66);
insert into mydata values(34,257.9);
insert into mydata values(34,205.86);
insert into mydata values(34,211.21);
insert into mydata values(34,252.7);
insert into mydata values(34,65.88);
insert into mydata values(34,102.28);
insert into mydata values(34,132.36);
insert into mydata values(34,139.19);
insert into mydata values(34,207.08);
insert into mydata values(34,181.68);
insert into mydata values(35,236.02);
insert into mydata values(35,259.31);
insert into mydata values(35,218.25);
insert into mydata values(35,256.24);
insert into mydata values(35,569.52);
insert into mydata values(35,119.1);
insert into mydata values(35,134.13);
insert into mydata values(35,130.57);
insert into mydata values(35,129.92);
insert into mydata values(35,149.88);
insert into mydata values(35,338.37);
insert into mydata values(36,190.24);
insert into mydata values(36,216.06);
insert into mydata values(36,205.88);
insert into mydata values(36,268.04);
insert into mydata values(36,314.98);
insert into mydata values(36,221.27);
insert into mydata values(36,194.12);
insert into mydata values(36,84.78);
insert into mydata values(36,123.48);
insert into mydata values(36,208.67);
insert into mydata values(36,196.6);
insert into mydata values(37,171.27);
insert into mydata values(37,171.98);
insert into mydata values(37,170.04);
insert into mydata values(37,417.29);
insert into mydata values(37,262.16);
insert into mydata values(37,72.24);
insert into mydata values(37,76.68);
insert into mydata values(37,88.86);
insert into mydata values(37,111.68);
insert into mydata values(37,108.55);
insert into mydata values(37,158.35);
insert into mydata values(38,184.21);
insert into mydata values(38,240.55);
insert into mydata values(38,549.68);
insert into mydata values(38,281.97);
insert into mydata values(38,257.89);
insert into mydata values(38,59.87);
insert into mydata values(38,84.81);
insert into mydata values(38,95.98);
insert into mydata values(38,112.77);
insert into mydata values(38,122.62);
insert into mydata values(38,215.99);
insert into mydata values(39,178.29);
insert into mydata values(39,206.46);
insert into mydata values(39,381.05);
insert into mydata values(39,374.12);
insert into mydata values(39,262.91);
insert into mydata values(39,47.16);
insert into mydata values(39,83.01);
insert into mydata values(39,109.49);
insert into mydata values(39,98.01);
insert into mydata values(39,117.18);
insert into mydata values(39,219.55);
insert into mydata values(40,259.63);
insert into mydata values(40,380.28);
insert into mydata values(40,156.43);
insert into mydata values(40,501.19);
insert into mydata values(40,100.06);
insert into mydata values(40,90.08);
insert into mydata values(40,96.98);
insert into mydata values(40,133.27);
insert into mydata values(40,202.61);
insert into mydata values(41,168.45);
insert into mydata values(41,170.62);
insert into mydata values(41,237.22);
insert into mydata values(41,253.93);
insert into mydata values(41,364.09);
insert into mydata values(41,238.51);
insert into mydata values(41,14.98);
insert into mydata values(41,63.19);
insert into mydata values(41,70.59);
insert into mydata values(41,122.46);
insert into mydata values(41,105.49);
insert into mydata values(41,161.37);
insert into mydata values(42,188.27);
insert into mydata values(42,277.81);
insert into mydata values(42,229.95);
insert into mydata values(42,269.21);
insert into mydata values(42,347.16);
insert into mydata values(42,267.35);
insert into mydata values(42,76.92);
insert into mydata values(42,73.91);
insert into mydata values(42,85.72);
insert into mydata values(42,110.19);
insert into mydata values(42,120.23);
insert into mydata values(42,118.85);
insert into mydata values(43,85.68);
insert into mydata values(43,135.74);
insert into mydata values(43,191.07);
insert into mydata values(43,199.65);
insert into mydata values(43,215.26);
insert into mydata values(43,264.36);
insert into mydata values(43,103.55);
insert into mydata values(43,109.37);
insert into mydata values(43,143.59);
insert into mydata values(43,127.75);
insert into mydata values(43,174.19);
insert into mydata values(43,186.84);
insert into mydata values(44,145.08);
insert into mydata values(44,118.6);
insert into mydata values(44,179.24);
insert into mydata values(44,176.59);
insert into mydata values(44,245.28);
insert into mydata values(44,213.42);
insert into mydata values(45,183.76);
insert into mydata values(45,160.98);
insert into mydata values(45,195.1);
insert into mydata values(45,159.05);
insert into mydata values(45,168.61);
insert into mydata values(45,165.33);
insert into mydata values(46,137.28);
insert into mydata values(46,171.94);
insert into mydata values(46,158.8);
insert into mydata values(46,176.45);
insert into mydata values(46,273.96);
insert into mydata values(46,165.85);
insert into mydata values(47,143.98);
insert into mydata values(47,136.87);
insert into mydata values(47,219.06);
commit;

*/



/*
WK1,	WK2,		WK3,		WK4,		WK5,		January,WK6,WK7,WK8,WK9,February
611.45,	2913.18,	2548.32,	2830.14,	2870.58,	11773.67, 3125.33, 3292.45, 3781.63, 2966.39, 13165.80
*/


select wk1, wk2, wk3, wk4, wk5,
	wk1+wk2+wk3+wk4+wk5 january,
	wk6, wk7, wk8, wk9,
	wk6+wk7+wk8+wk9 february
from (
	select si_week, ttl_income
	from myData
)
pivot 
(
   sum(ttl_income)
   for si_week in (1 as wk1,2 as wk2,3 as wk3,4 as wk4,5 as wk5,6 as wk6, 7 as wk7, 8 as wk8, 9 as wk9)
)
/

Open in new window

SQLSearcherAuthor Commented:
Hello Slightwv
I could really do with a loop solution so that the code will work year on year, with your solution I will have to rewrite the code each year.

Regards

SQLSearcher
slightwv (䄆 Netminder) Commented:
>> with your solution I will have to rewrite the code each year.

Are you saying the number of weeks that create "January" can change year to year?

If so I think there is a design issue.  The retail_weeks table should have a year column.  Otherwise you would not be able to run past years reports since the data in the retail weeks table might not work for the year you want.
slightwv (䄆 Netminder) Commented:
I suppose I should also ask:
What is the final destination for the results?

I probably can turn your pseudo--code into a stored procedure or function but I need to know where the results will eventually end up.  Functions need to return something and stored procedures need to do something with the results.
SQLSearcherAuthor Commented:
Hello Slightwv
The data will end up in a table.  I will create a new table for each year.

Regards

SQLSearcher
slightwv (䄆 Netminder) Commented:
>>I will create a new table for each year.

I think that is a bad design.  Then you need to keep a version of the query around for each individual year.

Anyway, If you create a new table each year then you will need to change the query anyway.

If you need to change the query anyway, why not use what I posted?
Mark GeerlingsDatabase AdministratorCommented:
I agree with slightwv.  A solution that involves creating a new table for each year, and changing the query each year is usually not the best way to solve a business problem like this.  Maybe in a Data Warehouse, if you have a query builder tool that can build queries dynamically, this might be a good way to go.  But, in most systems that I have worked in, this would not be the best solution to the problem.
slightwv (䄆 Netminder) Commented:
I had another thought:
>>The data will end up in a table.  I will create a new table for each year.

What will the destination table look like?

How will you know which column will be the Monthly rollup?
For 2014 January might be column 4
For 2015, it might be column 5
etc...


Can you not set the maximum allowed for the month and with the month doesn't have that many the value is 0?

For example:
The max January can have is 5 weeks so the 6th column would always bee January total.  For the years that only have 4 weeks for January, wk5 would be 0.

Then the SQL would never need to change.
SQLSearcherAuthor Commented:
Hi Slightwv
Thank you for the suggestion, but I am copying a report that already exists and need to mimic it as much as possible so having week 5 with a zero will not work for my report.

Regards

SQLSearcher
slightwv (䄆 Netminder) Commented:
What about the answers to my other questions?
Mark GeerlingsDatabase AdministratorCommented:
We understand your desire to mimic an existing report as much as possible.  But, sometimes trying to force an Oracle program (or query) to present information in exactly the same way it was presented by a different tool, makes for an Oracle program that is very difficult or complex to write and/or very in-efficient to run.

If the data you need to report on is in an Oracle database, it may make sense to use an approach that is simple to write, and efficient to execute in Oracle (even if this requires some user adjustment and/or training to see the data presented a bit differently that it was presented in the past).
slightwv (䄆 Netminder) Commented:
I also thought of something else that goes along with what Mark posted:

You stated above:  The data will end up in a table

Are you trying to insert data into a 'temp' table of some form to then generate a report from that?  If so, pay even more attention to Mark's post.  Other databases use temp tables for things like that but they typically don't port well into Oracle.
SQLSearcherAuthor Commented:
I will store the data in a table, if you believe it not best to store the data in Oracle I can store the data in SQL Server.

I am going to use SQL Server Reporting Service to display the data.
slightwv (䄆 Netminder) Commented:
I'm not a SQL Server person but I believe that SSRS can handle an Oracle cursor so there should be no need to store the data in a table.

Where you store the data isn't the issue.  The issue is storing a temporary result set when it isn't necessary.

I'm still waiting for answers to my previous questions:

First:
Anyway, If you create a new table each year then you will need to change the query anyway.

If you need to change the query anyway, why not use what I posted?



Second:
What will the destination table look like? (or columns in the cursor/result set)

 How will you know which column will be the Monthly rollup?
 For 2014 January might be column 4
 For 2015, it might be column 5
 etc...
SQLSearcherAuthor Commented:
Hello Slightwv
>>How will you know which column will be the Monthly rollup?
The Retail_Weeks table has a extra column that I did not include in my example which is year, so the Monthly rollup column will be Week_End + 1.

>>If you need to change the query anyway, why not use what I posted?
I think that if I use loops to run the code I will not have to change the code each year.

Regards

SQLSearcher
slightwv (䄆 Netminder) Commented:
You'll need to adjust this test case for your actual tables but here is what should be a truly dynamic version that is still all SQL.  No code or temp tables necessary.

The complete set setup is below.

The quick explanation is when the month might have 4 weeks instead of 5, just add the more complex CASE statement.

The weeks that are always the same (wk1 will ALWAYS be in month 1) can be simplified but I think it makes the code more readable.

Unless generating this at run time is just too slow for the report:
Since this can be a single select, there is no need for the destination table at all.  Just return the cursor and pass that to SSRS.

What we have been trying to point out this entire thread:
Don't store data unless you absolutely have to.  TEMP tables and code might be nice in other databases but they tend to be the "easy way" out. They also over complicate things and make performance much worse.

The query:
select
	max(case when rw_month=1 and si_week=1 then ttl_sum end) wk1,
	max(case when rw_month=1 and si_week=2 then ttl_sum end) wk2,
	max(case when rw_month=1 and si_week=3 then ttl_sum end) wk3,
	max(case when rw_month=1 and si_week=4 then ttl_sum end) wk4,
	max(case
		when rw_month=1 and si_week=5 then ttl_sum 
		when rw_month=2 and si_week=5 then month_sum 
	end) wk5,
	max(case
		when rw_month=1 and si_week=5 then month_sum 
		when rw_month=2 and si_week=5 then ttl_sum 
	end) wk6,
	max(case when rw_month=2 and si_week=6 then ttl_sum end) wk7,
	max(case when rw_month=2 and si_week=7 then ttl_sum end) wk8,
	max(case when rw_month=2 and si_week=8 then ttl_sum end) wk9,
	max(case
		when rw_month=2 and si_week=9 then ttl_sum 
		when rw_month=3 and si_week=9 then month_sum 
	end) wk10,
	max(case
		when rw_month=2 and si_week=9 then month_sum 
		when rw_month=3 and si_week=9 then ttl_sum 
	end) wk11
from(
	select rw_month, si_week, ttl_sum , sum(ttl_sum) over(partition by rw_month) month_sum
	from (
		select rw_month, si_week, sum(ttl_income) ttl_sum
		from (
			select rw_month, myweek, si_week, ttl_income
			from myData m
			join (
				--I'm not where I can get the version I use so I borrowed this from: https://community.oracle.com/thread/3585904
				select  rw_month, week_start + level - 1 myweek
				from    rw_weeks
				connect by   level <= week_end + 1 - week_start
					and  prior rw_month         = rw_month
					and  prior sys_guid()  is not null
			) r
			on m.si_week=r.myweek
		)
		where rw_month<3
		group by rw_month, si_week
	)
)
/

Open in new window



The setup:
/*
drop table myData purge;
create table myData(SI_Week number, TTL_Income number);
insert into mydata values(1,240.35);
insert into mydata values(1,192.99);
insert into mydata values(1,178.11);
insert into mydata values(2,216.79);
insert into mydata values(2,173.13);
insert into mydata values(2,216.16);
insert into mydata values(2,236.21);
insert into mydata values(2,207.95);
insert into mydata values(2,224.45);
insert into mydata values(2,52.22);
insert into mydata values(2,60.75);
insert into mydata values(2,100.75);
insert into mydata values(2,140.84);
insert into mydata values(2,124.92);
insert into mydata values(2,176.03);
insert into mydata values(2,123.67);
insert into mydata values(2,96.27);
insert into mydata values(2,130.89);
insert into mydata values(2,162.94);
insert into mydata values(2,225.27);
insert into mydata values(2,243.94);
insert into mydata values(3,131.58);
insert into mydata values(3,148.31);
insert into mydata values(3,222.72);
insert into mydata values(3,131.33);
insert into mydata values(3,235.81);
insert into mydata values(3,261.47);
insert into mydata values(3,34.01);
insert into mydata values(3,40.99);
insert into mydata values(3,68.22);
insert into mydata values(3,64.31);
insert into mydata values(3,259.91);
insert into mydata values(3,179.24);
insert into mydata values(3,79.83);
insert into mydata values(3,179.93);
insert into mydata values(3,96.75);
insert into mydata values(3,182.12);
insert into mydata values(3,231.79);
insert into mydata values(4,112.09);
insert into mydata values(4,144.62);
insert into mydata values(4,157.75);
insert into mydata values(4,257.72);
insert into mydata values(4,174.05);
insert into mydata values(4,240.25);
insert into mydata values(4,87.63);
insert into mydata values(4,154.38);
insert into mydata values(4,110.94);
insert into mydata values(4,100.15);
insert into mydata values(4,119.68);
insert into mydata values(4,242.64);
insert into mydata values(4,154.57);
insert into mydata values(4,95.84);
insert into mydata values(4,87.56);
insert into mydata values(4,143.01);
insert into mydata values(4,187.84);
insert into mydata values(4,259.42);
insert into mydata values(5,102.88);
insert into mydata values(5,207.33);
insert into mydata values(5,253.87);
insert into mydata values(5,183.93);
insert into mydata values(5,158.45);
insert into mydata values(5,101.56);
insert into mydata values(5,259.4);
insert into mydata values(5,173.98);
insert into mydata values(5,200.49);
insert into mydata values(5,262.55);
insert into mydata values(5,114.83);
insert into mydata values(5,117.28);
insert into mydata values(5,166.48);
insert into mydata values(5,389.59);
insert into mydata values(5,177.96);
insert into mydata values(6,147.86);
insert into mydata values(6,130.06);
insert into mydata values(6,137.52);
insert into mydata values(6,191.75);
insert into mydata values(6,242.62);
insert into mydata values(6,237.37);
insert into mydata values(6,194.3);
insert into mydata values(6,336.51);
insert into mydata values(6,174.25);
insert into mydata values(6,222.45);
insert into mydata values(6,249.38);
insert into mydata values(6,108.33);
insert into mydata values(6,117.99);
insert into mydata values(6,98.98);
insert into mydata values(6,174.3);
insert into mydata values(6,145.85);
insert into mydata values(6,215.81);
insert into mydata values(7,269.25);
insert into mydata values(7,169.49);
insert into mydata values(7,129.77);
insert into mydata values(7,246.31);
insert into mydata values(7,270.44);
insert into mydata values(7,150.02);
insert into mydata values(7,150.53);
insert into mydata values(7,154.6);
insert into mydata values(7,170.87);
insert into mydata values(7,239.25);
insert into mydata values(7,310.66);
insert into mydata values(7,96.58);
insert into mydata values(7,171.9);
insert into mydata values(7,186.88);
insert into mydata values(7,143.51);
insert into mydata values(7,153.36);
insert into mydata values(7,279.03);
insert into mydata values(8,147.05);
insert into mydata values(8,216.05);
insert into mydata values(8,185.63);
insert into mydata values(8,554.34);
insert into mydata values(8,344.92);
insert into mydata values(8,143.72);
insert into mydata values(8,162.13);
insert into mydata values(8,195.65);
insert into mydata values(8,125.58);
insert into mydata values(8,174.81);
insert into mydata values(8,337.2);
insert into mydata values(8,133.73);
insert into mydata values(8,361.68);
insert into mydata values(8,236.69);
insert into mydata values(8,170.8);
insert into mydata values(8,291.65);
insert into mydata values(9,157.7);
insert into mydata values(9,138.14);
insert into mydata values(9,178.21);
insert into mydata values(9,400.25);
insert into mydata values(9,211.97);
insert into mydata values(9,31.19);
insert into mydata values(9,119.73);
insert into mydata values(9,120.14);
insert into mydata values(9,304.45);
insert into mydata values(9,165.14);
insert into mydata values(9,125.54);
insert into mydata values(9,133.58);
insert into mydata values(9,148.38);
insert into mydata values(9,139.99);
insert into mydata values(9,239.21);
insert into mydata values(9,352.77);
insert into mydata values(10,116.7);
insert into mydata values(10,196.39);
insert into mydata values(10,209.44);
insert into mydata values(10,170.83);
insert into mydata values(10,276.97);
insert into mydata values(10,250.14);
insert into mydata values(10,94.67);
insert into mydata values(10,96.71);
insert into mydata values(10,133.08);
insert into mydata values(10,139.27);
insert into mydata values(10,147.3);
insert into mydata values(10,201.06);
insert into mydata values(10,218.24);
insert into mydata values(10,120.66);
insert into mydata values(10,173.08);
insert into mydata values(10,215.58);
insert into mydata values(10,184.95);
insert into mydata values(10,262.02);
insert into mydata values(11,102.54);
insert into mydata values(11,147.86);
insert into mydata values(11,129.49);
insert into mydata values(11,165.03);
insert into mydata values(11,172.08);
insert into mydata values(11,316.57);
insert into mydata values(11,106.84);
insert into mydata values(11,94.96);
insert into mydata values(11,125.93);
insert into mydata values(11,171.51);
insert into mydata values(11,224.12);
insert into mydata values(11,251.11);
insert into mydata values(11,223.46);
insert into mydata values(11,416.29);
insert into mydata values(11,126.43);
insert into mydata values(11,179.05);
insert into mydata values(11,359.88);
insert into mydata values(12,118.68);
insert into mydata values(12,229.95);
insert into mydata values(12,142.36);
insert into mydata values(12,194.66);
insert into mydata values(12,248.79);
insert into mydata values(12,330.56);
insert into mydata values(12,167.13);
insert into mydata values(12,158.77);
insert into mydata values(12,196.88);
insert into mydata values(12,230.45);
insert into mydata values(12,233.33);
insert into mydata values(12,244.91);
insert into mydata values(12,96.21);
insert into mydata values(12,190.86);
insert into mydata values(12,215.1);
insert into mydata values(12,148.17);
insert into mydata values(12,221.67);
insert into mydata values(12,298.23);
insert into mydata values(13,155.93);
insert into mydata values(13,153.16);
insert into mydata values(13,144.27);
insert into mydata values(13,167);
insert into mydata values(13,180.09);
insert into mydata values(13,231.09);
insert into mydata values(13,83.87);
insert into mydata values(13,128.77);
insert into mydata values(13,159.88);
insert into mydata values(13,172.93);
insert into mydata values(13,164.91);
insert into mydata values(13,239.55);
insert into mydata values(13,119.06);
insert into mydata values(13,291.52);
insert into mydata values(13,103.87);
insert into mydata values(13,172.31);
insert into mydata values(13,282.34);
insert into mydata values(13,122.02);
insert into mydata values(14,71.79);
insert into mydata values(14,158.15);
insert into mydata values(14,135.69);
insert into mydata values(14,171.49);
insert into mydata values(14,197.72);
insert into mydata values(14,258.56);
insert into mydata values(14,65.78);
insert into mydata values(14,72.47);
insert into mydata values(14,121.26);
insert into mydata values(14,165.21);
insert into mydata values(14,166.99);
insert into mydata values(14,324.45);
insert into mydata values(14,110.49);
insert into mydata values(14,129.69);
insert into mydata values(14,103.33);
insert into mydata values(14,369.72);
insert into mydata values(14,191.88);
insert into mydata values(15,143.89);
insert into mydata values(15,173.33);
insert into mydata values(15,134.29);
insert into mydata values(15,211.62);
insert into mydata values(15,283.32);
insert into mydata values(15,179.92);
insert into mydata values(15,156.69);
insert into mydata values(15,132.86);
insert into mydata values(15,218.85);
insert into mydata values(15,246);
insert into mydata values(15,228.07);
insert into mydata values(15,272.94);
insert into mydata values(15,94.73);
insert into mydata values(15,162.38);
insert into mydata values(15,149.1);
insert into mydata values(15,182.21);
insert into mydata values(15,198.17);
insert into mydata values(15,330.6);
insert into mydata values(16,150.53);
insert into mydata values(16,187.94);
insert into mydata values(16,178.22);
insert into mydata values(16,161.56);
insert into mydata values(16,225.94);
insert into mydata values(16,328.65);
insert into mydata values(16,107.86);
insert into mydata values(16,139.23);
insert into mydata values(16,114.25);
insert into mydata values(16,172.99);
insert into mydata values(16,227);
insert into mydata values(16,338.94);
insert into mydata values(16,195.43);
insert into mydata values(16,127.56);
insert into mydata values(16,399.33);
insert into mydata values(16,181.52);
insert into mydata values(16,249.25);
insert into mydata values(17,138.19);
insert into mydata values(17,185.92);
insert into mydata values(17,149.78);
insert into mydata values(17,177.91);
insert into mydata values(17,323.87);
insert into mydata values(17,228.41);
insert into mydata values(17,146.85);
insert into mydata values(17,134.63);
insert into mydata values(17,505.46);
insert into mydata values(17,293.65);
insert into mydata values(17,279.75);
insert into mydata values(17,40.92);
insert into mydata values(17,94.73);
insert into mydata values(17,122.01);
insert into mydata values(17,158.09);
insert into mydata values(17,218.97);
insert into mydata values(17,166.04);
insert into mydata values(17,188.28);
insert into mydata values(18,227.34);
insert into mydata values(18,187.32);
insert into mydata values(18,240.61);
insert into mydata values(18,179.52);
insert into mydata values(18,168.78);
insert into mydata values(18,345.53);
insert into mydata values(18,109.7);
insert into mydata values(18,122.32);
insert into mydata values(18,128.45);
insert into mydata values(18,138.56);
insert into mydata values(18,102.9);
insert into mydata values(18,269.71);
insert into mydata values(18,14.95);
insert into mydata values(18,128.24);
insert into mydata values(18,151.71);
insert into mydata values(18,149.31);
insert into mydata values(18,175.67);
insert into mydata values(18,204.61);
insert into mydata values(18,257.63);
insert into mydata values(19,179.49);
insert into mydata values(19,167.14);
insert into mydata values(19,157.23);
insert into mydata values(19,198.82);
insert into mydata values(19,241.92);
insert into mydata values(19,294.62);
insert into mydata values(19,44.4);
insert into mydata values(19,117.58);
insert into mydata values(19,157.59);
insert into mydata values(19,209.37);
insert into mydata values(19,152.07);
insert into mydata values(19,207.35);
insert into mydata values(19,272.22);
insert into mydata values(19,29.62);
insert into mydata values(19,85.23);
insert into mydata values(19,96.49);
insert into mydata values(19,143.43);
insert into mydata values(19,174.14);
insert into mydata values(19,202.57);
insert into mydata values(19,251.27);
insert into mydata values(20,164.11);
insert into mydata values(20,92.33);
insert into mydata values(20,195.17);
insert into mydata values(20,193.14);
insert into mydata values(20,361.1);
insert into mydata values(20,50.21);
insert into mydata values(20,86.98);
insert into mydata values(20,105.51);
insert into mydata values(20,136.17);
insert into mydata values(20,143.45);
insert into mydata values(20,170.26);
insert into mydata values(20,160.36);
insert into mydata values(20,36.11);
insert into mydata values(20,334.32);
insert into mydata values(20,132.75);
insert into mydata values(20,114.43);
insert into mydata values(20,195.89);
insert into mydata values(21,127.99);
insert into mydata values(21,123.67);
insert into mydata values(21,133.01);
insert into mydata values(21,343.3);
insert into mydata values(21,183.65);
insert into mydata values(21,55.94);
insert into mydata values(21,94.01);
insert into mydata values(21,142.5);
insert into mydata values(21,195.26);
insert into mydata values(21,200.39);
insert into mydata values(21,212);
insert into mydata values(21,302.04);
insert into mydata values(21,211.93);
insert into mydata values(21,106.17);
insert into mydata values(21,191.69);
insert into mydata values(21,169.63);
insert into mydata values(21,389.46);
insert into mydata values(22,166.16);
insert into mydata values(22,180.16);
insert into mydata values(22,141);
insert into mydata values(22,185.43);
insert into mydata values(22,226.89);
insert into mydata values(22,177.39);
insert into mydata values(22,55);
insert into mydata values(22,175);
insert into mydata values(22,185.13);
insert into mydata values(22,226.98);
insert into mydata values(22,408.68);
insert into mydata values(22,256.86);
insert into mydata values(22,94.13);
insert into mydata values(22,207.76);
insert into mydata values(22,158.18);
insert into mydata values(22,129.79);
insert into mydata values(22,190.94);
insert into mydata values(23,134.88);
insert into mydata values(23,153.96);
insert into mydata values(23,452.31);
insert into mydata values(23,188.34);
insert into mydata values(23,230.76);
insert into mydata values(23,71.05);
insert into mydata values(23,106.16);
insert into mydata values(23,125.73);
insert into mydata values(23,229.94);
insert into mydata values(23,164.08);
insert into mydata values(23,152.7);
insert into mydata values(23,127.43);
insert into mydata values(23,123.1);
insert into mydata values(23,135.35);
insert into mydata values(23,227.93);
insert into mydata values(23,252.94);
insert into mydata values(24,104.99);
insert into mydata values(24,133.55);
insert into mydata values(24,153.22);
insert into mydata values(24,176.22);
insert into mydata values(24,161.73);
insert into mydata values(24,206.93);
insert into mydata values(24,102.06);
insert into mydata values(24,187.38);
insert into mydata values(24,210.56);
insert into mydata values(24,507.31);
insert into mydata values(24,188.04);
insert into mydata values(24,314.32);
insert into mydata values(24,286.38);
insert into mydata values(24,135.35);
insert into mydata values(24,141.7);
insert into mydata values(24,162.95);
insert into mydata values(24,204.28);
insert into mydata values(25,114.03);
insert into mydata values(25,288.79);
insert into mydata values(25,154.54);
insert into mydata values(25,128.51);
insert into mydata values(25,175.58);
insert into mydata values(25,89.81);
insert into mydata values(25,119.65);
insert into mydata values(25,138.93);
insert into mydata values(25,167.78);
insert into mydata values(25,158.61);
insert into mydata values(25,199.13);
insert into mydata values(25,215.16);
insert into mydata values(25,84.52);
insert into mydata values(25,98.89);
insert into mydata values(25,133.82);
insert into mydata values(25,168.52);
insert into mydata values(25,163.13);
insert into mydata values(25,262.23);
insert into mydata values(26,148.07);
insert into mydata values(26,129.29);
insert into mydata values(26,179.39);
insert into mydata values(26,167.49);
insert into mydata values(26,210.71);
insert into mydata values(26,199.76);
insert into mydata values(26,122.34);
insert into mydata values(26,128.37);
insert into mydata values(26,181.89);
insert into mydata values(26,190.47);
insert into mydata values(26,326.93);
insert into mydata values(26,248.38);
insert into mydata values(26,429.5);
insert into mydata values(26,213.8);
insert into mydata values(26,160.87);
insert into mydata values(26,119.44);
insert into mydata values(26,263.87);
insert into mydata values(26,196.22);
insert into mydata values(27,96.65);
insert into mydata values(27,157.45);
insert into mydata values(27,176.41);
insert into mydata values(27,178.12);
insert into mydata values(27,197.38);
insert into mydata values(27,194.29);
insert into mydata values(27,68.77);
insert into mydata values(27,120.71);
insert into mydata values(27,311.89);
insert into mydata values(27,174.38);
insert into mydata values(27,223.52);
insert into mydata values(27,241.79);
insert into mydata values(27,106.64);
insert into mydata values(27,258.37);
insert into mydata values(27,138.39);
insert into mydata values(27,158.47);
insert into mydata values(27,128.54);
insert into mydata values(28,135.77);
insert into mydata values(28,118.08);
insert into mydata values(28,140.32);
insert into mydata values(28,130.57);
insert into mydata values(28,132.86);
insert into mydata values(28,162.48);
insert into mydata values(28,101.67);
insert into mydata values(28,118.48);
insert into mydata values(28,154.53);
insert into mydata values(28,104.08);
insert into mydata values(28,195.01);
insert into mydata values(28,216.27);
insert into mydata values(28,316.34);
insert into mydata values(28,156.23);
insert into mydata values(28,112.93);
insert into mydata values(28,176.39);
insert into mydata values(28,154.77);
insert into mydata values(28,166.45);
insert into mydata values(28,162.51);
insert into mydata values(29,110.61);
insert into mydata values(29,291.04);
insert into mydata values(29,151.99);
insert into mydata values(29,204.67);
insert into mydata values(29,201.31);
insert into mydata values(29,128.14);
insert into mydata values(29,129.53);
insert into mydata values(29,133.85);
insert into mydata values(29,179.8);
insert into mydata values(29,209.81);
insert into mydata values(29,222.56);
insert into mydata values(29,112.73);
insert into mydata values(29,102.28);
insert into mydata values(29,86.08);
insert into mydata values(29,120.87);
insert into mydata values(29,156.65);
insert into mydata values(29,121.63);
insert into mydata values(30,111.14);
insert into mydata values(30,242.7);
insert into mydata values(30,452.39);
insert into mydata values(30,181.9);
insert into mydata values(30,108.7);
insert into mydata values(30,147.27);
insert into mydata values(30,164.26);
insert into mydata values(30,153.8);
insert into mydata values(30,161.48);
insert into mydata values(30,213.48);
insert into mydata values(31,113.62);
insert into mydata values(31,136.77);
insert into mydata values(31,135.89);
insert into mydata values(31,137.86);
insert into mydata values(31,385.65);
insert into mydata values(31,121.98);
insert into mydata values(31,149.49);
insert into mydata values(31,195.75);
insert into mydata values(31,168.81);
insert into mydata values(31,175.11);
insert into mydata values(31,198.61);
insert into mydata values(32,151.14);
insert into mydata values(32,184.13);
insert into mydata values(32,231.57);
insert into mydata values(32,227.12);
insert into mydata values(32,269.5);
insert into mydata values(32,286.57);
insert into mydata values(32,132.02);
insert into mydata values(32,305.56);
insert into mydata values(32,144.56);
insert into mydata values(32,173.12);
insert into mydata values(32,214.8);
insert into mydata values(33,280.64);
insert into mydata values(33,180.65);
insert into mydata values(33,378.98);
insert into mydata values(33,209.48);
insert into mydata values(33,377.18);
insert into mydata values(33,91.14);
insert into mydata values(33,134.95);
insert into mydata values(33,104.97);
insert into mydata values(33,106.65);
insert into mydata values(33,161.41);
insert into mydata values(33,216.62);
insert into mydata values(34,385.66);
insert into mydata values(34,257.9);
insert into mydata values(34,205.86);
insert into mydata values(34,211.21);
insert into mydata values(34,252.7);
insert into mydata values(34,65.88);
insert into mydata values(34,102.28);
insert into mydata values(34,132.36);
insert into mydata values(34,139.19);
insert into mydata values(34,207.08);
insert into mydata values(34,181.68);
insert into mydata values(35,236.02);
insert into mydata values(35,259.31);
insert into mydata values(35,218.25);
insert into mydata values(35,256.24);
insert into mydata values(35,569.52);
insert into mydata values(35,119.1);
insert into mydata values(35,134.13);
insert into mydata values(35,130.57);
insert into mydata values(35,129.92);
insert into mydata values(35,149.88);
insert into mydata values(35,338.37);
insert into mydata values(36,190.24);
insert into mydata values(36,216.06);
insert into mydata values(36,205.88);
insert into mydata values(36,268.04);
insert into mydata values(36,314.98);
insert into mydata values(36,221.27);
insert into mydata values(36,194.12);
insert into mydata values(36,84.78);
insert into mydata values(36,123.48);
insert into mydata values(36,208.67);
insert into mydata values(36,196.6);
insert into mydata values(37,171.27);
insert into mydata values(37,171.98);
insert into mydata values(37,170.04);
insert into mydata values(37,417.29);
insert into mydata values(37,262.16);
insert into mydata values(37,72.24);
insert into mydata values(37,76.68);
insert into mydata values(37,88.86);
insert into mydata values(37,111.68);
insert into mydata values(37,108.55);
insert into mydata values(37,158.35);
insert into mydata values(38,184.21);
insert into mydata values(38,240.55);
insert into mydata values(38,549.68);
insert into mydata values(38,281.97);
insert into mydata values(38,257.89);
insert into mydata values(38,59.87);
insert into mydata values(38,84.81);
insert into mydata values(38,95.98);
insert into mydata values(38,112.77);
insert into mydata values(38,122.62);
insert into mydata values(38,215.99);
insert into mydata values(39,178.29);
insert into mydata values(39,206.46);
insert into mydata values(39,381.05);
insert into mydata values(39,374.12);
insert into mydata values(39,262.91);
insert into mydata values(39,47.16);
insert into mydata values(39,83.01);
insert into mydata values(39,109.49);
insert into mydata values(39,98.01);
insert into mydata values(39,117.18);
insert into mydata values(39,219.55);
insert into mydata values(40,259.63);
insert into mydata values(40,380.28);
insert into mydata values(40,156.43);
insert into mydata values(40,501.19);
insert into mydata values(40,100.06);
insert into mydata values(40,90.08);
insert into mydata values(40,96.98);
insert into mydata values(40,133.27);
insert into mydata values(40,202.61);
insert into mydata values(41,168.45);
insert into mydata values(41,170.62);
insert into mydata values(41,237.22);
insert into mydata values(41,253.93);
insert into mydata values(41,364.09);
insert into mydata values(41,238.51);
insert into mydata values(41,14.98);
insert into mydata values(41,63.19);
insert into mydata values(41,70.59);
insert into mydata values(41,122.46);
insert into mydata values(41,105.49);
insert into mydata values(41,161.37);
insert into mydata values(42,188.27);
insert into mydata values(42,277.81);
insert into mydata values(42,229.95);
insert into mydata values(42,269.21);
insert into mydata values(42,347.16);
insert into mydata values(42,267.35);
insert into mydata values(42,76.92);
insert into mydata values(42,73.91);
insert into mydata values(42,85.72);
insert into mydata values(42,110.19);
insert into mydata values(42,120.23);
insert into mydata values(42,118.85);
insert into mydata values(43,85.68);
insert into mydata values(43,135.74);
insert into mydata values(43,191.07);
insert into mydata values(43,199.65);
insert into mydata values(43,215.26);
insert into mydata values(43,264.36);
insert into mydata values(43,103.55);
insert into mydata values(43,109.37);
insert into mydata values(43,143.59);
insert into mydata values(43,127.75);
insert into mydata values(43,174.19);
insert into mydata values(43,186.84);
insert into mydata values(44,145.08);
insert into mydata values(44,118.6);
insert into mydata values(44,179.24);
insert into mydata values(44,176.59);
insert into mydata values(44,245.28);
insert into mydata values(44,213.42);
insert into mydata values(45,183.76);
insert into mydata values(45,160.98);
insert into mydata values(45,195.1);
insert into mydata values(45,159.05);
insert into mydata values(45,168.61);
insert into mydata values(45,165.33);
insert into mydata values(46,137.28);
insert into mydata values(46,171.94);
insert into mydata values(46,158.8);
insert into mydata values(46,176.45);
insert into mydata values(46,273.96);
insert into mydata values(46,165.85);
insert into mydata values(47,143.98);
insert into mydata values(47,136.87);
insert into mydata values(47,219.06);
commit;

drop table rw_weeks purge;
create table rw_weeks (RW_Month number, No_Weeks number, Week_Start number, Week_End number);

insert into rw_weeks values(1,5,1,5);
insert into rw_weeks values(2,4,6,9);
insert into rw_weeks values(3,5,10,14);
insert into rw_weeks values(4,4,15,18);
insert into rw_weeks values(5,4,19,22);
insert into rw_weeks values(6,5,23,27);
insert into rw_weeks values(7,4,28,31);
insert into rw_weeks values(8,4,32,35);
insert into rw_weeks values(9,5,36,40);
insert into rw_weeks values(10,4,41,44);
insert into rw_weeks values(11,4,45,48);
insert into rw_weeks values(12,5,49,53);
*/

Open in new window

Mark GeerlingsDatabase AdministratorCommented:
That looks elegant!  I didn't have time to figure out the code for you, but this approach of doing the job in one SQL statement with no "temp" table is almost always the best starting point in Oracle.  The only reason for not using this technique is if "at run time [this] is just too slow".  We can't test the response time for this in your system.

If this is too slow too to be acceptable, or if multiple people need to run it and are unable to share the results from a single run, then it may make sense to either use a "temp" or staging table for part of this process, or even to build a permanent table to hold the results for each year.
SQLSearcherAuthor Commented:
Hello Slightwv
How should I end the select statement when there maybe 53 weeks and when there is only 52 weeks in a year?

Regards

SQLSearcher
slightwv (䄆 Netminder) Commented:
I'm not following.  Can you provide sample data and expected results that cover the end of the year with 53 weeks?
SQLSearcherAuthor Commented:
Hi Slightwv
Please find data attached.

Regards

SQLSearcher
Data.xls
slightwv (䄆 Netminder) Commented:
I don't see the expected results.
SQLSearcherAuthor Commented:
Hi Slightwv
Sorry about that, please see Sheet2.

Regards

SQLSearcher
Data2.xls
slightwv (䄆 Netminder) Commented:
>>How should I end the select statement when there maybe 53 weeks and when there is only 52 weeks in a year?

You'll just need to have a null column.

For a year that has a 53rd week, The last column will be the total.  When it only has 53 weeks, the last column will be null.

Even for the code-based solution you originally proposed, there has to be a pre-defined number of columns for the result set returned.
SQLSearcherAuthor Commented:
Hello Slightwv
I am thinking that the solution you have given me is not going to work for my problem.

I have attached a sample of data from the retail_weeks table and as you can see month 12 can start on several different weeks depending on what year it is, which with your code is going to be hard to take care of.

I think I need a loop solution that I have suggested before, can you help me with this?

Regards

SQLSearcher
Data3.xls
Mark GeerlingsDatabase AdministratorCommented:
I'm wondering how you get six weeks in December in 2012, 2013 and 2014.  When I look at a calendar, I see portions of six weeks in December in 2007 and 2012, but not in 2013 and 2014.  What criteria is used in your system to find portions of six weeks in December in 2013 and 2014?
slightwv (䄆 Netminder) Commented:
Since you seem set on using a loop and nothing we can say has changed your mind, here is a simple loop.

I had to change from your original pseudo code because as posted, it doesn't won't work.

You are storing both the week and sum and month and sum in temp_table1.

There is no way to know which "1" is week 1 or month 1.
1  12345
1   34567

Here is the solution I came up with.  I build a CSV string to return.  You could just as easily build an XML document or anything.

Yes, you could even populate a table that you create for that specific year's run if you had to.  I STRONGLY encourage to to not do it but it is your system so you can do whatever you wish.

It uses the table setup and data I created above.
declare
	type ttl_type is table of number index by pls_integer;
	week_totals	ttl_type;
	month_total	number;

	result_csv	varchar2(4000);

begin
	for i in ( select si_week, sum(ttl_income) ttl from mydata group by si_week) loop
		week_totals(i.si_week) := i.ttl;
	end loop;

	for i in (select rw_month, week_start, week_end from rw_weeks order by rw_month) loop
		month_total := 0;
		for k in  i.week_start .. i.week_end loop
			begin
				month_total := month_total + week_totals(k);
				result_csv := result_csv || to_char(week_totals(k)) || ',';
				exception when no_data_found then
					result_csv := result_csv || '0,';
			end;
		end loop;
		result_csv := result_csv || to_char(month_total) || ',';
	end loop;
	dbms_output.put_line(rtrim(result_csv,','));
end;
/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLSearcherAuthor Commented:
Hello slightwv
Is there a way of getting the results from my dbms_output window to my query window?

Regards

SQLSearcher
slightwv (䄆 Netminder) Commented:
I only use sqlplus.  What tool are you using?

Most tools follow sqlplus settings so they should recognize this:
set serveroutput on

Look in the settings for serveroutput.  Some tools even have a dbms_output window separate from the query results window.
SQLSearcherAuthor Commented:
Thank you for all your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.