Solved

The Trouble With Queries

Posted on 2016-09-22
19
40 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 69

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 24

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 69

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
 
LVL 24

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 69

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 24

Author Comment

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

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:ScottPletcher
ScottPletcher 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 24

Author Comment

by:dgrafx
ID: 41811369
What code are you referring to Jim?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 65

Expert Comment

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

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 24

Author Comment

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

Expert Comment

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

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 24

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
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 …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now