Check this procedure - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Check this procedure

Author

Topic

s
Starting Member

5 Posts

Posted - 2003-02-26 : 04:21:40

Hi all

can any one check this procedure and let me know how to make smalll
because i have total 28 codes to check ....v001,m006,m007...
it is working fine but in the report i have to insert all the of the
each district,site for all the code..so if i go on writing like
this i get 28*4 ..output paramters and also it is not easy to
debug the procedure ..so let me know any other way for this

i want the data like this to show in the report

district site code1 code2
year02 year01 ix per year02 year01 ix per
1000 200 10 20
100 20 30

total 2 30 50

2000 500 45 45
400 50 50

total 2 30 50

=======================================
CREATE PROCEDURE dbo.cdh_spDynamicReport (@intYear1 smallint,@intYear2

smallint, @intMonth TinyInt) AS
BEGIN

select t_V001_1.district_type, t_V001_1.sun_site,
t_V001_1.Amount as 'V001 2002', t_V001_2.Amount 'V001 2001',

t_V001_1.Amount - t_V001_2.Amount as 'Improve V001', t_v001_1.Amount

/(t_V001_1.Amount - t_V001_2.Amount) * 100 as 'Percentage1',
t_m006_1.Amount as 'M006 2002', t_m006_2.Amount 'M006 2001',

t_m006_1.Amount - t_m006_2.Amount as 'Improve M006', t_m006_1.Amount

/(t_m006_1.Amount - t_m006_2.Amount) * 100 as 'Percentage2'

from

(
select district_type, sun_site, oper_sundata.map_code, sum(amount)/1000 as

Amount
from oper_type_new, oper_sundata
where oper_type_new.sun_site = oper_sundata.site_id and
(district_type is not null and district_type <> '' )
and oper_sundata.oper_month = @intMonth and

oper_sundata.oper_year= @intYear1
and oper_sundata.map_code = 'v001'
group by district_type,sun_site, oper_sundata.map_code
) as t_V001_1

left join

(select district_type, sun_site, oper_sundata.map_code, sum(amount)/1000 as

Amount
from oper_type_new, oper_sundata
where oper_type_new.sun_site = oper_sundata.site_id and
(district_type is not null and district_type <> '' )
and oper_sundata.oper_month = @intMonth and

oper_sundata.oper_year= @intYear2
and oper_sundata.map_code = 'v001'
group by district_type,sun_site, oper_sundata.map_code
) as t_V001_2

on t_V001_1.district_type = t_V001_2.district_type and
t_V001_1.sun_site = t_V001_2.sun_site and
t_V001_1.map_code = t_V001_2.map_code

left join

(
select district_type, sun_site, oper_sundata.map_code, sum(amount)/1000 as

Amount
from oper_type_new, oper_sundata
where oper_type_new.sun_site = oper_sundata.site_id and
(district_type is not null and district_type <> '' )
and oper_sundata.oper_month = @intMonth and

oper_sundata.oper_year= @intYear1
and oper_sundata.map_code = 'm006'
group by district_type,sun_site, oper_sundata.map_code
) as t_m006_1

on t_V001_1.district_type = t_m006_1.district_type and
t_V001_1.sun_site = t_m006_1.sun_site

left join

(
select district_type, sun_site, oper_sundata.map_code, sum(amount)/1000 as

Amount
from oper_type_new, oper_sundata
where oper_type_new.sun_site = oper_sundata.site_id and
(district_type is not null and district_type <> '' )
and oper_sundata.oper_month = @intMonth and

oper_sundata.oper_year= @intYear2
and oper_sundata.map_code = 'm006'
group by district_type,sun_site, oper_sundata.map_code
) as t_m006_2

on

t_m006_1.district_type = t_m006_2.district_type and
t_m006_1.sun_site = t_m006_2.sun_site and
t_m006_1.map_code = t_m006_2.map_code

order by t_V001_1.district_type, t_V001_1.sun_site

END
GO
===============================================================

s

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-26 : 10:08:24

Haven't looked at this carefully but maybe you could put all the codes into a temp table.
Build all the data into another temp table from this.
Then use a crosstab to return the results.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources