Q
Problem solve Get help with specific problems with your technologies, process and projects.

Year-to-date period totals

I have a table with 12 columns labeled Period1 to Period12. All columns contain numerical values. 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.

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.

This was last published in April 2006

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close