Finding the relevent price for a given date

[myan]

FoxPro Version 8
I have a table with rooms and a date they were occupied.


D_ALLOC:

C_ID C_Room D_Eff C_RateID
0001 Room1 {30/01/2000} 0001
0002 Room2 {10/01/2001} 0002
0003 Room3 {15/01/2000} 0002

I also have a table that contains rates

D_RATES

C_RateID D_From N_Amount
0001 {01/01/1999} 100
0001 {01/01/2000} 200
0001 {01/01/2001} 300
0002 {01/01/1999} 110
0002 {01/01/2001} 120

I now want to find the rate that suits the room at the correct date:

Result would be:

C_ID C_Room D_Eff C_RateID N_amount
0001 Room1 {30/01/2000} 0001 200
0002 Room2 {10/01/2001} 0002 120
0003 Room3 {15/01/2000} 0002 110

I was trying to get something like this to work:

[tt]
SELECT C_ID,C_ROOM,R.C_RATEID, R.N_Amount ;
FROM D_ALLOC, D_RATES AS R;
WHERE D_Alloc.C_rateid = R.C_rateid ;
AND d_from <= ;
(SELECT MAX(d_from) FROM d_RATES ;
WHERE D_Alloc.C_RateID = d_RATES.C_RateID ;
AND D_Alloc.d_eff <= d_RATES.d_from GROUP BY C_RateID) ;
INTO CURSOR curBill

[/tt]

But FoxPro does not allow this type of query. Any help would be appreciated.
Paul



Some code for creating the tables:
[tt]
CREATE CURSOR D_ALLOC (C_ID C(4), C_Room C(10) ,D_Eff D(10),C_RateID C(4))

INSERT INTO D_ALLOC (C_ID, C_Room, D_Eff, C_RateID) ;
VALUES (&quot;0001&quot;,&quot;RM1&quot;,DATE(2000,01,30),&quot;0001&quot
INSERT INTO D_ALLOC (C_ID, C_Room, D_Eff, C_RateID) ;
VALUES (&quot;0002&quot;,&quot;RM2&quot;,DATE(2001,01,10),&quot;0002&quot
INSERT INTO D_ALLOC (C_ID, C_Room, D_Eff, C_RateID) ;
VALUES (&quot;0003&quot;,&quot;RM3&quot;,DATE(2000,01,15),&quot;0002&quot

CREATE CURSOR D_RATES (C_RateID C(4),D_From D(10), N_Amount n(4))

INSERT INTO D_RATES (C_RateID, D_From, N_Amount) ;
VALUES (&quot;0001&quot;,DATE(1999,01,01),100)
INSERT INTO D_RATES (C_RateID, D_From, N_Amount) ;
VALUES (&quot;0001&quot;,DATE(2000,01,01),200)
INSERT INTO D_RATES (C_RateID, D_From, N_Amount) ;
VALUES (&quot;0001&quot;,DATE(2001,01,01),300)
INSERT INTO D_RATES (C_RateID, D_From, N_Amount) ;
VALUES (&quot;0002&quot;,DATE(1999,01,01),110)
INSERT INTO D_RATES (C_RateID, D_From, N_Amount) ;
VALUES (&quot;0002&quot;,DATE(2001,01,01),120)

[/tt]

 

[Mike Gagnon]

myan

I am not sure of the logic of this, but try this, if you are going for the MAX date on rate 2, wouldn't the answer be 120 and not 110?

SELECT C_ID,C_ROOM,R.C_RATEID, R.N_Amount ;
FROM D_ALLOC, D_RATES AS R;
WHERE D_Alloc.C_rateid = R.C_rateid ;
AND d_from <= (SELECT MAX(d_from) FROM d_RATES);
group by 2;
INTO CURSOR curBill

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[myan]

Yes its the max rate but it has to be the Max rate at the effective date as per column D_eff in table D_alloc.

In the case of Room 3 the rate was 110 at the date {15/01/2000} the room was allocated.

D_rates.D_from {01/01/1999} is the max date a rate was a available prior to the {15/01/2000) 'Confused'