I have a table with 12 columns labeled Period1 to Period12. All columns contain numerical values.
Period1 | Period2 | Period3 etc. 1000 2000 500
I would like to create a function, or stored procedure, which you can pass in the Current Period and create a Year to Date total.
Year to Date for Period 2 - YTDTotal 3000 Year to Date for Period 3 - YTDTotal 3500 etc.
Hope you can answer this. Have looked at other answers, and all are based on summing a fixed number of columns. This needs to be more dynamic.
Here's what you will need --
select Period1 +Period2 from ... select Period1 +Period2 +Period3 from ... etc.
The way to make this "dynamic" is to generate the SQL in your application code (whether this is in a stored procedure or whatever). This won't be truly dynamic because you will need to EXEC the dynamic query string.
A better solution is to design your table differently. Instead of 12 columns, create a table with two columns -- Period Number and Amount. Then in Period 1 there would be one row, in Period 2 you add another row, and so on. In this way, you can make the SQL truly dynamic, like this --
select sum(Amount) from ... where PeriodNumber <= 2 select sum(Amount) from ... where PeriodNumber <= 3 etc.
Now, that's a lot nicer, isn't it? And by making the period number a parameter, the query can be compiled in the stored proc and so be even more efficient.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.