Select max(date) ??? 1

[straatlang]

I hope somebody can help me with the next problem.

Table: movements.

Value Date Account Amount Cur DRCR
1-1-2003 123 2000 EUR CR
1-2-2003 456 1500 EUR CR
1-2-2003 123 1450 USD CR
1-4-2003 456 1250 EUR CR
1-7-2003 123 800 USD CR
15-1-2003 123 950 EUR CR
20-1-2003 789 5000 USD CR
27-1-2003 456 5000 USD CR
28-1-2003 456 3000 USD CR
29-1-2003 123 10000 EUR DT


Table :names

Account Name
123 ABC
456 DEF
789 GHJ

Table : rates

CurID Erate
EUR 1
USD 0.98
GBP 0.34

The query I am using now works properly but not if there is one rate per currency per day.

I am using the following Query (thanks to Rudy R937)

SELECT Movements.Account, Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, Movements.Cur, IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, [Enter Value Date] AS SelectedDate, Rates.Erate, Names.Name, [Erate]*[Balance] AS V_EURO
FROM (Movements INNER JOIN [Names] ON Movements.Account = Names.Account) INNER JOIN Rates ON Movements.Cur = Rates.CurID
WHERE (((Movements.[Value Date])<=DateValue([Enter Value Date])))
GROUP BY Movements.Account, Movements.Cur, Rates.Erate, Names.Name;

So the table rates now looks like this.

CurID Erate RateDate
EUR 1 01-01-2003
USD 0.95 01-01-2003
GBP 0.65 02-01-2003
USD 0.98 02-01-2003
GBP 0.66 03-01-2003
USD 0.98 03-01-2003
GBP 0.34 03-01-2003
ect


The query should show the balances per account per currency per <=Valuedate. And the balance as
EUR

The expected outcome per valuedate should be:

Account Name Balance Cur FLAG SelectedDate Erate V_EURO
123 ABC 7050 EUR DR 1 7050
123 DEF 2250 USD CR 0.98 2205
456 DEF 2750 EUR CR 1 2750
456 DEF 8000 USD CR 0.98 7840
789 GHJ 5000 USD CR 0.98 4900

Regards

Straatlang

 

[swampBoogie]

You mean like

SELECT M.Account, 
       Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, 
       M.Cur, 
       IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, 
       [Enter Value Date] AS SelectedDate, 
       R.Erate, 
       N.Name, 
       Erate*Balance AS V_EURO
  FROM (Movements M INNER JOIN Names N ON M.Account = N.Account) 
                    INNER JOIN Rates R ON M.Cur = R.CurID
 WHERE M.[Value Date] <= DateValue([Enter Value Date])
   and R.rateDate = (select max(rateDate) from rates
                      where curID = R.curID
                        and rateDate <= M.[Value Date] )
 GROUP BY M.Account, 
          M.Cur, 
          R.Erate, 
          N.Name

 

[straatlang]

Almost correct.

If I enter 31-01-2003 as the valuedate the result is:

Cur FLAG SelectedDate Erate Name V_EURO
EUR DR 1 ABC 7050
USD CR 0.98 ABC 2205
EUR CR 1 DEF 2750
USD CR 0.9 DEF 7200
USD CR 0.95 GHJ 4750

The rate table is:

ID CurID Erate RateDate
1 EUR 1 1-1-2003
2 USD 0.98 1-1-2003
3 GBP 0.34 1-1-2003
4 EUR 1 2-1-2003
5 USD 0.95 2-1-2003
6 EUR 1 25-1-2003
7 USD 0.9 25-1-2003

So then every balance in USD should have the same rate in this case 0.9 (25-01-2003) which is the rate &quot;nearest&quot; to 31-01-2003.

Can you help me out.

Regards

Straatlang

 

[straatlang]

Sorry here are all the details which I get when entering the Value date as 31-01-2003.

So now Account 123 his rate for EUR/USD is 0.98 456 gets 0.90 and 789 get 0.95.



Account Balance Cur FLAG SelectedDate Erate Name V_EURO
123 7050 EUR DR 1 ABC 7050
123 2250 USD CR 0.98 ABC 2205
456 2750 EUR CR 1 DEF 2750
456 8000 USD CR 0.9 DEF 7200
789 5000 USD CR 0.95 GHJ 4750



The rate table is:

ID CurID Erate RateDate
1 EUR 1 1-1-2003
2 USD 0.98 1-1-2003
3 GBP 0.34 1-1-2003
4 EUR 1 2-1-2003
5 USD 0.95 2-1-2003
6 EUR 1 25-1-2003
7 USD 0.9 25-1-2003

So then every balance in USD should have the same rate in this case 0.9 (25-01-2003) which is the rate &quot;nearest&quot; to 31-01-2003.

Can you help me out.