Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

The Trouble With Queries

Posted on 2016-09-22
19
Medium Priority
?
72 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 1000 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 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 66

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 66

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

704 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