query - 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

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-09-28 : 09:32:50

hi
i have written a sp to insert some records into one table
and from this table i generate a report.
its working fine. it takes around 4+ minutes.
i take sum(column1) from table1 for individual branch id.
around 1000 branches are there. so it insert 1000 records.
my question is can i have a single query which does this job
so that report can be generated faster.

i am giving the query which selects the record for individual branh id. there are around 1 lac records in the table1.

/*loop (cursor)*/

select sum(column1) as c1,
(SELECT SUM(column1) FROM Table1
WHERE BRANCH_CODE=@brnCode AND ASSET_TYPE=5 group by
branch_code) as c2,
(SELECT SUM(column1) FROM Table1
WHERE BRANCH_CODE=@brnCode AND ASSET_TYPE=6
group by branch_code) as c3,
(SELECT SUM(column1) FROM Table1
WHERE BRANCH_CODE=@brnCode AND ASSET_TYPE=7 group by
branch_code) as c4,
(SELECT SUM(column1) FROM Table1
WHERE BRANCH_CODE=@brnCode AND ASSET_TYPE=9
group by branch_code) as c5 from
Table1 where asset_type=4 and
branch_code=@brnCode group by branch_code

/* close cursor*/

thanks.

suji

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 09:35:46

You can do something like this, which avoids all the sub queries, but I don't know if it solves all the performance issues which COULD be solved!


select sum(column1) as c1,
	SUM(CASE WHEN BRANCH_CODE=@brnCode AND ASSET_TYPE=5 THEN column1 ELSE 0 END) AS c2,
	...
FROM Table1

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 09:39:05

Why can't you use GROUP BY on branch_code in single query:

Select
	sum(col1) as c1,
	sum( case when Asset_Type = 5 then col2 else 0 end) as c2,
	sum( case when Asset_Type = 6 then col2 else 0 end) as c3
	sum( case when Asset_Type = 7 then col2 else 0 end) as c4
	...
From Table1

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-28 : 09:40:12

Read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-09-28 : 13:15:35

thanks for the reply. i think cross tab query will solve my problem

suji

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources