Need Help with a view - 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 with a view

Author

Topic

aikman
Starting Member

22 Posts

Posted - 2008-07-06 : 15:16:07

Table download last page: http://www.boltfile.com/download/c11e6497

I should create 1 view that display both the 5 best sales persons (highest bonus) and after that the 5 sales persons that have sold most las month. it´s displayed in table bokning colum tidpunkt (date).

I have to do this in just 1 view.

Bonus i calculated provision * provisionsandel and should be displayed by person. You can an example on "uppgift 3" in the .pdf file.

Thanks all foe help, I really cant understand what to do.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-06 : 21:10:28

Please post the expected result of the view required

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 00:06:37

something like:-

SELECT * FROM
(SELECT TOP 5 Person,SUM(provision * provisionsandel ) AS Bonus,'Highest Bonus' AS Category
FROM YourTable
GROUP BY Person
ORDER BY Bonus DESC)t

UNION ALL

SELECT *
FROM
(SELECT TOP 5 Person,SUM(salescalculationhere) AS Sales,'Highest Sales' AS Category
FROM YourTable
GROUP BY Person
ORDER BY Sales DESC)t1

please provide more info if you need more precise soln.

aikman
Starting Member

22 Posts

Posted - 2008-07-07 : 03:06:19

Example on how it could look like:
SELECT * from “my View”

Type quantity/commission salesID SalesPerson

Provision 1584 36 Sandra Karsson
Provision 1453 30 Zoe Baum
Provision 1354 38 Zula Lovejoy
Provision 1264 22 Soo Mendoza
Provision 1034 18 Sonja Schreck
Quantity Customers 66 30 Zoe Baum
Quantity Customers 63 19 Ceola Rockett
Quantity Customers 63 6 Esbjorn Adamsson
Quantity Customers 61 13 Maria Ahlgren
Quantity Customers 60 10 Ronda Brann

(10 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 03:40:39

Bonus i calculated provision * provisionsandel

whats the field for provisionsandel?

aikman
Starting Member

22 Posts

Posted - 2008-07-07 : 04:12:36

Table bokning

bokning_id produkt_id saljare_id kund_id tidpunkt
1 8 13 895 2005-10-09 00:00:00.000
2 2 5 40 2005-11-12 00:00:00.000
3 5 22 364 2005-10-31 00:00:00.000
4 2 2 5 2006-04-22 00:00:00.000
5 10 15 429 2006-03-23 00:00:00.000
6 6 18 209 2005-10-05 00:00:00.000
7 6 15 476 2006-04-10 00:00:00.000
8 7 7 233 2005-12-18 00:00:00.000
9 10 38 79 2006-03-05 00:00:00.000

Table product

produkt_id namn provision
1 42" Plasma TV 1000
2 Nokia 6150 500
3 Telefonabonnemang 100
4 19" LCD skärm 500
5 EU moped 3000

Table lonekat

lonekat_id beskrivning manadslon provisionsandel
1 Ingångslön 10000 0.10
2 Junior säljare 15000 0.40
3 Säljare 20000 0.60
4 Senior säljare 25000 0.80

table personal

pers_id chef_id lönekategori förnamn efternamn Titel
1 34 1 Johan Forsberg Säljare
2 35 1 Carl Johnson Säljare
3 29 1 Erik Persson Säljare
4 30 1 Jonas Svegerud Säljare
5 31 1 Petter Solberg Säljare
6 32 1 Esbjörn Adamnsson Säljare
7 33 1 Lorriane Barksdale Säljare
8 34 2 Johannes Agerberg Säljare
9 35 2 Christoffer Andersson Säljare
10 29 2 Peter Svensson Säljare
11 30 2 Tobias Petterson Säljare
12 31 2 Sofia Nilsson Säljare
13 32 2 Maria Ahlgren Säljare

To calculate Bonus you have to look what they have sold in table bokning.produkt_id (every row is one sold unit), and in table produkt colum provision shows how much every
product has in commission. Then in table lonekat colum provisionsandel shows the percent % that the employee get in from the total commission that he has sold for.

I´ll hope you understand now, i know i don´t :S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources