Need help in building 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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help in building Query

Author

Topic

mayurcreation
Starting Member

16 Posts

Posted - 2007-09-13 : 10:03:54

hi

I have table Salesperson with following columns:
Id, Name, Year, Target, Actual, Perc_Achived
1, AA, 2007, 10000, '', ''
2, BB, 2007, 20000, '', ''
3, CC, 2007, 10000, '', ''
4, DD, 2007, 30000, '', ''

I hava another table MarketValue with following columns:
account, value, salesperson, open_dt
xx, 2000, AA/BB, 02/25/2007
yy, 5000, CC/AA, 03/30/2003
zz, 6000, BB/DD, 8/1/2004
mm, 3000, AA, 9/1/2007
nn, 2000, CC, 5/5/2006
yy, 7000, CC/AA, 03/30/2007
zz, 6000, BB/DD, 8/1/2007
....
....
....

I have percentage slipt between salespersons in different table SalespersonDetail :
cust3, salesperson, perc_account
AA/BB, aa, 25
AA/BB, bb, 75
BB/DD, bb, 30
BB/DD, dd, 70
CC/AA, aa, 50
CC/AA, cc, 50
AA, AA, 100
BB, BB, 100
CC, CC, 100
DD, DD, 100

I need to (sum the value of account) as Actual with sales person perc_account where open_dt is in 2007 for all sales person and update Salesperson table with Actual and Perc_Achived.

I have write down following query which give me result.

select s.salesperson as Name, sum ((m.value * s.perc_of_account)/100) as Actual
from marketvalue m , salespersondetail s
where m.salesperson_id = s.cust3
and DATEPART(year, [open_dt]) = '2007'
group by s.salesperson

Result
------
Name Actual
AA 107262115.73000000
BB 62198250.87000000
CC 75329154.53000000
DD 212301243.82000000

I dont know now how to update this result into Salesperson table. Name, Actual.

Any help will be really appreciated...

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-13 : 10:23:27

Some examples of updates from join

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85022
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72460

PS: If anyone is interested in a SQL Server job in Connecticut with excellent pay send me a resume to ValterBorges@msn.com

mayurcreation
Starting Member

16 Posts

Posted - 2007-09-13 : 10:45:37

the below two links are no good for me. As in my query there is group and sum is involved with is on third table.

The update line which I need to set Actual = (is sum - formula).
eg
Update Salesperson
set actual = sum ((m.value * s.perc_of_account)/100)
from Salesperson s

Please help me out here...

Thanks.

quote:
Originally posted by ValterBorges

Some examples of updates from join

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85022
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72460

PS: If anyone is interested in a SQL Server job in Connecticut with excellent pay send me a resume to ValterBorges@msn.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources