Solved

The Trouble With Queries

Posted on 2016-09-22
19
58 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Add a step to a system backup job 6 18
SQL Query 2 31
Query for timesheet application 3 17
SSRS: Why is Visual Studio stripping these properties? 2 21
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

856 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