Help with efficient coding (part of Dynamic Query in SP)

Question

Post reply

Help with efficient coding (part of Dynamic Query in SP)

KK-375826

SSC Veteran

Points: 287
More actions
June 28, 2007 at 2:12 pm

#124517

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
from
(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
JOIN
(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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Your solution appears to be convoluted - selects nested within selects are not good.

create table Exports2001

(Districtinteger not null

,Teritoryinteger not null

, stat_monthinteger not null

, all_val_month integer not null

, constraint Exports2001_P primary key (District, Teritory , stat_month)

)

go

-- Create the other tables for years 2002 thru 2006

selectDistrict

,DistrictExportAmt2001

,DistrictExportAmt2002

,DistrictExportAmt2003

,DistrictExportAmt2004

,DistrictExportAmt2005

,DistrictExportAmt2006

,( DistrictExportAmt2002 - DistrictExportAmt2001 ) * 100 / DistrictExportAmt2001 as Growth2002

,( DistrictExportAmt2003 - DistrictExportAmt2002 ) * 100 / DistrictExportAmt2002 as Growth2003

,( DistrictExportAmt2004 - DistrictExportAmt2003 ) * 100 / DistrictExportAmt2003 as Growth2004

,( DistrictExportAmt2005 - DistrictExportAmt2004 ) * 100 / DistrictExportAmt2004 as Growth2005

,( DistrictExportAmt2006 - DistrictExportAmt2005 ) * 100 / DistrictExportAmt2005 as Growth2006

,DistrictExportAmt2002 * 100 / TotalExportAmt2002 as Share2002

,DistrictExportAmt2003 * 100 / TotalExportAmt2003 as Share2003

,DistrictExportAmt2004 * 100 / TotalExportAmt2004 as Share2004

,DistrictExportAmt2005 * 100 / TotalExportAmt2005 as Share2005

,DistrictExportAmt2006 * 100 / TotalExportAmt2006 as Share2006

FROM(

-- pivot the rows

selectDistrict

,MAX( CASE when ExportYear = 2001 then ExportAmt else null end ) as DistrictExportAmt2001

,MAX( CASE when ExportYear = 2002 then ExportAmt else null end ) as DistrictExportAmt2002

,MAX( CASE when ExportYear = 2003 then ExportAmt else null end ) as DistrictExportAmt2003

,MAX( CASE when ExportYear = 2004 then ExportAmt else null end ) as DistrictExportAmt2004

,MAX( CASE when ExportYear = 2005 then ExportAmt else null end ) as DistrictExportAmt2005

,MAX( CASE when ExportYear = 2006 then ExportAmt else null end ) as DistrictExportAmt2006

FROM(

-- combine the yearly tables into a single logical table

select 2006 , District, sum(all_val_month) from Exports2006 group by district union all

select 2005 , District, sum(all_val_month) from Exports2005 group by district union all

select 2004 , District, sum(all_val_month) from Exports2004 group by district union all

select 2003 , District, sum(all_val_month) from Exports2003 group by district union all

select 2002 , District, sum(all_val_month) from Exports2002 group by district union all

select 2001 , District, sum(all_val_month) from Exports2001 group by district )

as DistrictAnnualExports (ExportYear, District, ExportAmt )

group by District

) AS DistrictExports

CROSS JOIN

(

-- pivot the rows

selectMAX( CASE when ExportYear = 2001 then ExportAmt else null end ) as TotalExportAmt2001

,MAX( CASE when ExportYear = 2002 then ExportAmt else null end ) as TotalExportAmt2002

,MAX( CASE when ExportYear = 2003 then ExportAmt else null end ) as TotalExportAmt2003

,MAX( CASE when ExportYear = 2004 then ExportAmt else null end ) as TotalExportAmt2004

,MAX( CASE when ExportYear = 2005 then ExportAmt else null end ) as TotalExportAmt2005

,MAX( CASE when ExportYear = 2006 then ExportAmt else null end ) as TotalExportAmt2006

from (

-- combine the yearly tables into a single logical table

select 2006 , sum(all_val_month) from Exports2006 union all

select 2005 , sum(all_val_month) from Exports2005 union all

select 2004 , sum(all_val_month) from Exports2004 union all

select 2003 , sum(all_val_month) from Exports2003 union all

select 2002 , sum(all_val_month) from Exports2002 union all

select 2001 , sum(all_val_month) from Exports2001

) TotalAnnualExports (ExportYear, ExportAmt )

) as TotalExports

SQL = Scarcely Qualifies as a Language

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 2

Here's a general case which may help. I'll build it incrementally so you can see the process.

Create the table and populate it:

CREATE TABLE dbo.TotalsTest(
    Mon     int NOT NULL,
    Value   int NOT NULL
);

-- Just throw some values in there
insert into dbo.TotalsTest(
    Mon, Value )
select 1, 1  union all
select 2, 10 union all
select 3, 15 union all
select 4, 20 union all
select 1, 20 union all
select 2, 15 union all
select 3, 10 union all
select 4, 5  union all
select 1, 15 union all
select 2, 25 union all
select 3, 15 union all
select 4, 5  union all
select 1, 25 union all
select 2, 10 union all
select 3, 35 union all
select 4, 15;

It's easy to group the months and get totals for each month.

select  Mon, Sum( Value ) as Total
from    dbo.TotalsTest
group by Mon;

But you want each month to have its own column so:

select  Mon,
    case when Mon = 1 then Value else 0 end as ValMonth1,
    case when Mon = 2 then Value else 0 end as ValMonth2,
    case when Mon = 3 then Value else 0 end as ValMonth3,
    case when Mon = 4 then Value else 0 end as ValMonth4
from    dbo.TotalsTest;

See? Now use that query as a derived table and just add each column -- you don't even have to group them:

select
    Sum( ValMonth1 ) as Total1,
    Sum( ValMonth2 ) as Total2,
    Sum( ValMonth3 ) as Total3,
    Sum( ValMonth4 ) as Total4
from(
    select  Mon,
        case when Mon = 1 then Value else 0 end as ValMonth1,
        case when Mon = 2 then Value else 0 end as ValMonth2,
        case when Mon = 3 then Value else 0 end as ValMonth3,
        case when Mon = 4 then Value else 0 end as ValMonth4
    from    dbo.TotalsTest
    ) as SpreadOut;

One row, each month in it's own column. This should be easy to make work with your table.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

KK-375826 SSC Veteran Points: 287 More actions · Answer 3

Thanks for your replies. I will try them out during the weekend and get back if I am not able to do it. Once again thanks.