Combining dataset with if statement and calculating results

Transact-SQL
1

Hi
So I have a query to get my maximum date and rate, now somehow I need to get the rate into my general ledger entries to output results.

But first it needs to know what rate table to look at ie so if the entity in general ledger field begins with 'C' it looks at chf table, or 'e' then eur table......then it takes the foreign ccy amount in general ledger and converts to max date in ccy table ie with right combination so for example entity begins with E and trans ccy is usd then it should find the maximum date of usd to eur rate.

There is a further complication in that if FCY amount is blank I want it to always take the FCY as gbp and use the amount field to calculate. Ie if entity is beginning with e and FCY amount is blank then take the rate gbp to eur (ie entity begins e so eur ) and calculate it on amount field ( not FCY amount as above ) divided my that rate.

This is what I have so far....please can someone help ? Or is too complex.... Thank you so much.

select t.CurrencyFrom, t.CurrencyTo, t.FXDate, t.FXRate
from _Test t
inner join
-- Find max date
(select CurrencyFrom, CurrencyTo, max(FXDate) FXDate from _Test group by CurrencyFrom, CurrencyTo) ma
on ma.CurrencyFrom = t.CurrencyFrom and ma.CurrencyTo = t.CurrencyTo and ma.FXDate = t.FXDate

2

It is not too complex if you can define the problem in a way that is easy for someone to understand. Create some sample tables that people can copy and paste to run, and then also give the desired output. For example like this. It is a completely bogus, contrived example that may absolutely have no resemblance to your actual data/tables. It is meant only to show you how to post the DDL and sample data.

-- this is my chf table                
CREATE TABLE #CHF( amount FLOAT, TradeDate DATE);
INSERT INTO #CHF 
        ( amount, TradeDate )
VALUES  ( 11000, '20150902'),(12054,'20150904'),(2500, '20150905');

-- this is my GL table
CREATE TABLE #GL (GLDate DATE, LedgerField VARCHAR(32));
INSERT INTO #GL
	(GLDate, LedgerField)
VALUES	('20150902', 'CSomething'), ('20150903','ESomething'), ('20150903', NULL);

--
SELECT * FROM #CHF;
SELECT * FROM #GL;
3

Thank you I have an excel document prepared of the tables so will upload this weekend.
I really appreciate help. I think if I master this I will master quite a bit.

4

Hmmm ... what will happen when you need to add a new currency? The APP will have to be changed. It would be better to have a Currency Table which has a column for "Ledger Currency Short Code" that will match your "C" or "E"

If these tables, specific to currency, already exist and you cannot change them (some 3rd party APP) then I would create a VIEW that combines them:

CREATE VIEW MyViewName
AS
SELECT   'C' AS CurrencyShortCode
       , 'CHF' AS CurrencyLongCode
       , FXDate
       , ... all the rest of the columns ...
FROM MyChfTable
UNION ALL
SELECT   'E'
       , 'EUR'
       , FXDate
       , ... all the rest of the columns ...
FROM MyEurTable
UNION ALL
...

then you can just do

JOIN [MyViewName]
ON CurrencyShortCode = LEFT([general ledger field], 1)	-- 'C', 'E', etc.
AND ... other column(s) ...

one way to do this (i.e. find the "most recent date" in an associate one-to-many table relationship) is

SELECT	P.Col1, P.Col2, C.Col3, C.Col4, ...
FROM	MyParentTable AS P
	OUTER APPLY
	(
		SELECT TOP 1 C.Col3, C.Col4, ...
		FROM	MyChildTable AS C
		WHERE	    C.PKeyCol1 = P.PKeyCol1
			AND C.PKeyCol2 = P.PKeyCol2
			AND ...
		ORDER BY C.SomeDate DESC, C.SomeUniqueID_TieBreak
	) AS C
ORDER BY P.Col1, ...