Step away from that cursor and back away slowly...

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)

Step away from that cursor and back away slowly...

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2004-08-18 : 16:57:56

I'm trying to avoid using a cursor.

I have two tables.

SemiconWest2004

Relevant Fields:
AccountNumber Int
Company_Name Varchar(100)

and

Sales_History

Relevant Fields:
AccountNumber int
Partnumber Varchar(50)
Quantity int
TotalsaleNet float

I want the following data:

AccountNumber & Company Name
Each distinct partnumber they bought
The total number of quantity they purchased of that part - sum(quantity)
The total sales of each part - convert(Money, sum(total_salenet))

My problem

When I join on the accountnumber, because there might be more than one instance of an accountnumber I get sums that are out of whack.

The way I currently solved the problem is this but I didn't know if there was a better solution.

select accountnumber, max(company_name) as company_name
into #temp1 
 from semiconwest2004
	where accountnumber is not null
	group by accountnumber
select 
	a.AccountNumber, max(company_name), Partnumber, sum(Quantity), convert(varchar, convert(money, sum(TotalSaleNet), 50)) as TotalSales
from 
	#temp1 a 
join
	smc_sales.dbo.sales_history b
on 
	a.accountnumber=b.accountnumber
group by 
	a.accountnumber, Partnumber
order by a.accountnumber

Any suggestions on how to improve are very welcome.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-18 : 17:08:55

derived tables.



Select
	A.accountNumber,
	B.Company_Name,
	A.PartNumber,
	A.TotalQty,
	A.TotalSales
From 
	(
	Select 
		accountNumber,
		PartNumber,
		TotalQty = sum(Quantity),
		TotalSales = convert(money,sum(TotalSaleNet))
	From Sales_History as s
	Group By accountNumber
	) as A
Left Join SemiconWest2004 as B
On A.accountNumber = B.accountNumber

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources