Find current tax_rate based on a date in another table

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

SQL Server Development (2000)

Find current tax_rate based on a date in another table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-03 : 08:09:54

Ian writes "I have a table of orders and a table of tax rates, where the tax rate can change over time. I want to write a select statement that will return one row per order, showing the tax rate that was valid on the order date.

I think I need a subquery to find the correct row from tax_rates but I can't find how to include the order_date in the subquery

Example data
Table - tax_rates


product_id	country	effective_date	tax_rate
PR01		UK	01-Jan-04		10%
PR01		UK	31-Dec-04		17%
PR01		UK	17-Feb-05		22%
PR01		UK	01-Apr-05		15%
PR01		JP	01-Jan-04		35%

Table – orders

	
order_no	order_date	product_id	country
1 	01-Jan-05		PR01		UK
2 	28-Feb-05		PR01		UK
3 	02-Mar-05		PR01		UK
4 	14-Feb-05		PR01		JP

We can join the tables on product_id and country, but then each order may join to several rows with different effective_dates.
The correct row to return is the one with the *highest effective date that is <= order_date*

Required result


order_no	order_date	product_id	country	tax_rate
1	01-Jan-05		PR01		UK	17%
2	28-Feb-05		PR01		UK	22%
3	02-Mar-05		PR01		UK	22%
4	14-Feb-05		PR01		JP	35%

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 08:22:56

join tax_rates r
on r.product_id = o.product_id
and r.country = o.country
and r.effective = (select max(r2.effective) from tax_rate r2 where r2.effective <= o.order_date and r2.product_id = r.product_id and r2.country =r.country)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources