Test records from one table / query against a date range in another table

  • Thread starter Thread starter Green Biro
  • Start date Start date
G

Green Biro

Hi there. Hoping that someone can assit me with this:

Can get recordset of dated orders ie <Date, OrderId, CustomerId, Charge> but
I need to check (and apply) discounts from a discounts table that has the
format <CustomerId, StartDate, EndDate, %age discount> Of course if no
appropruate record were found in the discounts table then the full cost
needs to be charged.

My output needs to be <Date, OrderId, CustomerId, Charge, NewCharge>

I thought about trying to create a temporary 'complete' discounts table with
every possible date and customer combination but I'm sure there's a better
way. Please can someone point me in the right direction.

I would prefer a query that can be represented in Design View but just SQL
will do.

Many thanks.

GB

PS I should add that I will be writing something thet ensures that no
overlapping records can exist in the discounts table
 
Try these two queries --
GreenBiro_1 --
SELECT Order.OrderDate, Order.CustomerId, Discounts.[%age discount]
FROM [Order] INNER JOIN Discounts ON Order.CustomerId = Discounts.CustomerId
WHERE (((Order.OrderDate) Between [StartDate] And [EndDate]));

SELECT Order.OrderDate, Order.OrderId, Order.CustomerId, Order.Charge,
IIf([%age discount] Is Null,[Charge],[Charge]-([Charge]*[%age discount])) AS
NewCharge
FROM [Order] LEFT JOIN GreenBiro_1 ON (Order.OrderDate =
GreenBiro_1.OrderDate) AND (Order.CustomerId = GreenBiro_1.CustomerId);
 
Thank you very much. This works perfectly. I have this concept coming up
in various parts of my application and you've given me a definite way
forward.

Most indebted.

GB

KARL DEWEY said:
Try these two queries --
GreenBiro_1 --
SELECT Order.OrderDate, Order.CustomerId, Discounts.[%age discount]
FROM [Order] INNER JOIN Discounts ON Order.CustomerId =
Discounts.CustomerId
WHERE (((Order.OrderDate) Between [StartDate] And [EndDate]));

SELECT Order.OrderDate, Order.OrderId, Order.CustomerId, Order.Charge,
IIf([%age discount] Is Null,[Charge],[Charge]-([Charge]*[%age discount]))
AS
NewCharge
FROM [Order] LEFT JOIN GreenBiro_1 ON (Order.OrderDate =
GreenBiro_1.OrderDate) AND (Order.CustomerId = GreenBiro_1.CustomerId);

--
Build a little, test a little.


Green Biro said:
Hi there. Hoping that someone can assit me with this:

Can get recordset of dated orders ie <Date, OrderId, CustomerId, Charge>
but
I need to check (and apply) discounts from a discounts table that has the
format <CustomerId, StartDate, EndDate, %age discount> Of course if no
appropruate record were found in the discounts table then the full cost
needs to be charged.

My output needs to be <Date, OrderId, CustomerId, Charge, NewCharge>

I thought about trying to create a temporary 'complete' discounts table
with
every possible date and customer combination but I'm sure there's a
better
way. Please can someone point me in the right direction.

I would prefer a query that can be represented in Design View but just
SQL
will do.

Many thanks.

GB

PS I should add that I will be writing something thet ensures that no
overlapping records can exist in the discounts table
Click to expand...
Click to expand...
 
Back
Top