Tough SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Tough SQL Query

Author

Topic

dtmorton
Starting Member

3 Posts

Posted - 2008-09-02 : 09:41:34

I have two tables. One with sales data, and another with commission amounts. I'd like to do a single query and return the appropriate commission. Realize that there won't be a perfect match between percentageofretail. I want the highest commission where the percofretail is not lower than the actual. I give an example with data below.

Table definitions

invoice

InvoiceNumber, Invoice Date, PercentageofRetail, InvoiceAmount
1--------------08/12/2008---------- .95--------------500
2--------------08/12/2008---------- .80--------------300
3--------------08/12/2008----------1.00--------------500

CommissionRate

PercofRetail, CommissionRate

1.00------------- .30
.90-------------- .25
.80-------------- .2
.70-------------- .1

What I Want returned
Invoice Number Invoice Date Invoice Amt Percofretail Commperc
1 --------------08/12/2008-------500--------- .95-------- .3
2 --------------08/12/2008-------300--------- .80-------- .2
3 --------------08/12/2008-------500-------- 1.00-------- .3

So there is only one row for each invoice which shows the appropriate commperc from the commissionrate table

I know I can do this programatically, but was hoping one of you geniuses, had a way to do this strictly through SQL.

Thanks in advance for your help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 09:43:25

So which class are you attending?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 09:46:11

Use this correlated subquery

(SELECT TOP 1 x.CommissionRate FROM CommissionRate AS x WHERE x.PercOfRetail >= Invoice.PercOfRetail ORDER BY x.PercOfRetail DESC) AS CommPerc

E 12°55'05.63"
N 56°04'39.26"

dtmorton
Starting Member

3 Posts

Posted - 2008-09-02 : 09:57:53

Thanks Peso, I will try this suggestion. Believe it or not, this is for a commission program for a business. It does look like a class project thoguh doesn't it. :) Thanks again I'll try this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 10:03:39

select *,
(SELECT MAX(x.CommissionRate) FROM CommissionRate AS x WHERE x.PercOfRetail >= Invoice.PercOfRetail) AS CommPerc
from invoice

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources