Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
miamikk
Starting Member
19 Posts |
 Posted - 2007-06-28 : 16:11:16
|
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_GrowthRatefrom(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 Total1FROM [2006exp] a where a.stat_month=1 and a.district=1Group by a.district) tyJOIN(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 Total2FROM [2005exp] b where b.stat_month=1 and b.district=1Group by b.district) py on ty.district=py.district |
|
|
|
|
|
|
|
Help with efficient coding (Dynamic Query/SP)