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 2005 Forums

Transact-SQL (2005)

Tough SQL Query

Author

Topic

dtmorton
Starting Member

3 Posts

Posted - 2008-09-02 : 00:22:09

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-02 : 00:32:33

[code]
SELECT *
FROM Invoice i
CROSS apply
(
SELECT TOP 1 CommissionRate
FROM CommissionRate c
ORDER BY abs(i.PercentageofRetail - c.PercofRetail), CommissionRate DESC
) c[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources