Solved

The Trouble With Queries

Posted on 2016-09-22
19
53 Views
Last Modified: 2016-09-27
Sql 2K8
See attachments for tables

Problem: How to query to show bonus when sales exceed tier level for each year

Thanks much!
salesperson.sql
orders.sql
bonus_pay.sql
visual.png
0
Comment
Question by:dgrafx
  • 8
  • 5
  • 2
  • +2
19 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41811151
could it be something like (untested):

select SP.ID, SP.Name, A.Amount, BP.Bonus
from (
   select salesperson_id, year(order_date) as OrderYear, sum(Amount) as Amount
   from orders
   group by salesperson_id, year(order_date) 
)  AS A
inner join Bonus_Pay as BP
on BP.Year = A.OrderYear
and BP.Tier < A.Amount
inner join SalesPerson AS SP
on SP.ID = A.Salessperson_ID

Open in new window

0
 
LVL 25

Author Comment

by:dgrafx
ID: 41811165
Thanks
See results in attachment.
I need to show 1 row only for each salesperson as each salesperson only gets the one bonus - the higher one.
I didn't say that earlier - I apologize.
results.png
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41811205
that will make it:
SELECT *
FROM (
	SELECT SP.ID, SP.Name, A.OrderYear, A.Amount, BP.Bonus
	, RANK() OVER(PARTITION BY SP.ID, A.OrderYear ORDER BY bp.Bonus DESC) AS rank
	from (
	   select salesperson_id, year(order_date) as OrderYear, sum(Amount) as Amount
	   from orders
	   group by salesperson_id, year(order_date) 
	)  AS A
	inner join Bonus_Pay as BP
	on BP.Year = A.OrderYear
	and BP.Tier < A.Amount
	inner join SalesPerson AS SP
	on SP.ID = A.Salesperson_ID
) AS G
WHERE G.rank = 1	

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 25

Author Comment

by:dgrafx
ID: 41811214
ok perfect thanks

one final q - would you say that this is an efficient query or is there more to do for more efficiency?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41811221
Probably a CTE could help a bit for efficiency. Also joining to the Salesperson table only in the outside query would help a bit.
0
 
LVL 25

Author Comment

by:dgrafx
ID: 41811227
example would be awesome!
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 250 total points
ID: 41811232
that would be:
;
WITH ordersCTE
as
(
	SELECT A.salesperson_id, A.OrderYear, A.Amount, BP.Bonus
	, RANK() OVER(PARTITION BY A.salesperson_id, A.OrderYear ORDER BY bp.Bonus DESC) AS rank
	from (
	   select salesperson_id, year(order_date) as OrderYear, sum(Amount) as Amount
	   from orders
	   group by salesperson_id, year(order_date) 
	)  AS A
	inner join Bonus_Pay as BP
	on BP.Year = A.OrderYear
	and BP.Tier < A.Amount
) 

SELECT SP.ID, SP.Name, O.OrderYear, O.Amount, O.Bonus 
FROM ordersCTE AS O
inner join SalesPerson AS SP
on SP.ID = O.Salesperson_ID
WHERE rank = 1	

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 41811234
SELECT o.salesperson_id, sp.Name AS salesperson_name, o.Year, ISNULL(bonus_lookup.Bonus, 0) AS Bonus
FROM (
    SELECT salesperson_id, YEAR(order_date) AS Year, SUM(Amount) AS orders_total
    FROM dbo.Orders
    GROUP BY salesperson_id, YEAR(order_date)
) AS o
INNER JOIN dbo.SalesPerson sp ON sp.ID = o.salesperson_id
OUTER APPLY (
    SELECT TOP (1) Bonus
    FROM dbo.Bonus_Pay bp
    WHERE bp.Year = o.Year AND bp.Tier <= o.orders_total
    ORDER BY bp.Tier DESC
) AS bonus_lookup


For best performance:
cluster the Orders table on order_date first, then id.
cluster the bonus table on (Year, Tier) and not ID.
0
 
LVL 25

Author Comment

by:dgrafx
ID: 41811369
What code are you referring to Jim?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41811380
Your original question has three .sql code files and one .png image file.
0
 
LVL 25

Author Comment

by:dgrafx
ID: 41811404
And you're saying that attachments are supposed to be in a code block!?!
Why is there the ability for attachments if that is the case?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41811440
>And you're saying that attachments are supposed to be in a code block!?!
We greatly perfer it, as with four attachments we'd have to click 8-9 times to see everything.

>Why is there the ability for attachments if that is the case?
Good question.  Don't exactly know, other than to attach other file types such as Excel spreadsheets, Access databases, or XML that do not have a button to render it well.
0
 
LVL 25

Author Comment

by:dgrafx
ID: 41811446
OK that's fine
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41811472
do you need more help with this question?
0
 
LVL 25

Author Closing Comment

by:dgrafx
ID: 41811773
Thanks very much - greatly appreciated!
0
 
LVL 2

Expert Comment

by:DonRameshSachin
ID: 41811916
one more solution to the problem - if interested.


;with cteEmps
as
(
      select s.name, s.ID  , year(o.order_date) YYYY, sum(o.Amount) Amount
      from [Salesperson] s
            join [Orders] o
                  on s.ID = o.salesperson_id
      group by s.name, s.ID  , year(o.order_date)
)
, cteFinal
as
(
      select e.*,
      (Select top 1 Bonus From [Bonus_Pay] B where b.[Year] = E.YYYY
       and b.Tier< e.Amount
       order by [Year], Tier desc
      ) Bonus
      from cteEmps E
)

select * from cteFinal where Bonus is not null order by 1
0
 
LVL 25

Author Comment

by:dgrafx
ID: 41812607
More than one way to skin a tribble eh?

Thanks Don
0
 
LVL 2

Expert Comment

by:DonRameshSachin
ID: 41819055
:D
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question