Effective dates and discounts

[PaulBirch]

Hi there,

I am hoping you can help me with a little puzzle I have here. I have two tables:

1. Transaction Table

Order Date, Customer, Amount
========================================
01/01/2003 BILL 10.00
02/01/2003 BILL 20.00
03/01/2003 BILL 30.00

2. Discount Table

Customer, Effective Date, Discount
==========================================
BILL 01/01/2003 10%
BILL 02/01/2003 20%
BILL 03/01/2003 50%

I want to be able to look up the discount that applies for an order using the effective date.
Eg, have the query return something like this:

Customer, Order Date, Amount, Discount
==========================================================
BILL 01/01/2003 10.00 10%
BILL 02/01/2003 20.00 20%
BILL 03/01/2003 30.00 50%

I have tried the following query - but I keep getting duplicated rows as soon as I add the discount column to the first query:

select t.[order date],
t.amount,
d.discount
from transactions t, discount d
where t.customer = 'BILL'
and [order date] >= ( SELECT Max(d.[effective date])
from discount d
where d.[effective date] <= t.[order date]
)


Any help would be appreciated.

Regards,

Paul.

 

[PaulBirch]

Doesn't matter I fixed it.... Duh!

For your info:


select t.[order date],
t.amount,
d.discount,
d.[effective date]
from transactions t, discount d
where t.customer = 'BILL'
and t.customer = d.customer
and t.[order date] >= d.[effective date]
and d.[effective date] >= ( SELECT Max([effective date])
from discount
where [effective date] <= t.[order date]
)

Regards,

Paul

 

[tlbroadbent]

I know you solved the problem but you may prefer this query. It is a bit simpler.

Select
t.[order date],
t.amount,
d.discount,
d.[effective date]
From transactions t
Join discount d
On t.customer = d.customer
Where t.customer = 'BILL'
And d.[effective date] =
(SELECT Max([effective date])
FROM discount
WHERE customer = t.customer
AND [effective date] <= t.[order date]) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.