• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

The Trouble With Queries

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
dgrafx
Asked:
dgrafx
  • 8
  • 5
  • 2
  • +2
2 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
dgrafxAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
dgrafxAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
dgrafxAuthor Commented:
example would be awesome!
0
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
dgrafxAuthor Commented:
What code are you referring to Jim?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Your original question has three .sql code files and one .png image file.
0
 
dgrafxAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
dgrafxAuthor Commented:
OK that's fine
0
 
Éric MoreauSenior .Net ConsultantCommented:
do you need more help with this question?
0
 
dgrafxAuthor Commented:
Thanks very much - greatly appreciated!
0
 
DonRameshSachinCommented:
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
 
dgrafxAuthor Commented:
More than one way to skin a tribble eh?

Thanks Don
0
 
DonRameshSachinCommented:
:D
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now