A problem with Dynamic SQL

[mobius_function]

hi All,

At work, we have this particular table defined as follows :

CREATE TABLE sales_data (
sales_id NUMBER,
sales_m01 NUMBER,
sales_m02 NUMBER,
sales_m03 NUMBER,
sales_m04 NUMBER,
sales_m05 NUMBER,
sales_m06 NUMBER,
sales_m07 NUMBER,
sales_m08 NUMBER,
sales_m09 NUMBER,
sales_m10 NUMBER,
sales_m11 NUMBER,
sales_m12 NUMBER,
sales_prior_yr NUMBER );
/

The columns 'sales_m01 ..... sales_m12' represents aggregated monthly sales, in which 'sales_m01' translates to 'sales for the month of january, january being the first month, 'sales_m02' sales for the month of february, and so on.

The problem I face is that we have a project which requires that a parameter be passed to a stored procedure which stands for the month number which is then used to build a SQL query with the following required aggregations :

Sample 1 : parameter input: 4
Dynamically-built SQL query should be :

SELECT
SUM(sales_m04) as CURRENT_SALES,
SUM(sales_m01+sales_m02+sales_m03+sales_m04) SALES_YTD
FROM
sales_data
WHERE
sales_id = '0599768';

Sample 2 : parameter input: 8
Dynamically-built SQL query should be :

SELECT
SUM(sales_m08) as CURRENT_SALES,
SUM(sales_m01+sales_m02+sales_m03+sales_m04+
sales_m05+sales_m06+sales_m07+sales_m08) SALES_YTD
FROM
sales_data
WHERE
sales_id = '0599768';

Any solution is greatly appreciated.
Thanks.
Mobius_Function.

[skhanal]

You can rewrite this a procedure

declare @sql varchar(255), @mon int, @cur varchar(25), @ytd varchar(100), @salesid varchar(10)

set @mon=4
set @salesid='0599768'

set @cur='SUM(sales_m'+substring('0'+rtrim(cast(@mon as char(2))),1,2)+')'
--print @cur

set @ytd = 'SUM('
while @mon > 0
begin
set @ytd=@ytd+'sales_m'+substring('0'+rtrim(cast(@mon as char(2))),1,2)+'+'
set @mon=@mon-1
end
set @ytd=substring(@ytd,1,len(@ytd)-1)+')'
--print @ytd

set @sql =
'SELECT ' + @cur +
' as CURRENT_SALES,' + @ytd + '
as SALES_YTD
FROM
sales_data
WHERE
sales_id = '''+@salesid+''''

print @sql