I need help with this code in making it more efficient (part of Dynamic SQL query inside Stored Procedure). The code takes about 2-3 min to generate output. I am a newbie to SQL programming and I would appreciate if anyone can rewirite the code for the solution I need.
The output of this code needs to generate a table with columns from present year data plus columns with previous 4 years of data. Each column values are calculated from that particular year table & its prevoius year table. The data tables are named sequentially as (2000exp, 2001exp ....2005exp, 2006exp). The final output should like the image below.
I wrote the code that calculates values for 1 column and I am just repeating the same code 4 times for remaining 4 other columns. This definitely not an efficient way as most of the code is repeated. The code is shown in the image below. I highlighted repeatitive code with same colors
The main stored procedure will get 3 inputs (tablename, month and district like 2006exp, 12, 1). Part of complete SP code is shown in image below. The part of code I need help will be for case 0.
I would appreciate if anyone can help me. The code that generates values for 1 column is below.
Select ty.Amount1 as Amount, ((ty.Amount1-py.Amount2)/py.Amount2)*100 as GrowthRate,
(ty.Amount1/ty.Total1)*100 as Share, ty.Total1 as Total, ((ty.Total1-py.Total2)/py.Total2)*100 as Total_GrowthRate
(Select a.district, Sum(a.all_val_mo) as Amount1, (select Sum(a1.all_val_mo) FROM [2006exp] a1 where a1.stat_month <=1) as Total1
FROM [2006exp] a where a.stat_month=1 and a.district=1
Group by a.district) ty
(Select b.district, Sum(b.all_val_mo) as Amount2, (select Sum(b1.all_val_mo) FROM [2005exp] b1 where b1.stat_month <=1) as Total2
FROM [2005exp] b where b.stat_month=1 and b.district=1
Group by b.district) py on ty.district=py.district