troubleshooting Question

SQL to take the last date (newest) in a list and use its values in a calculation

Avatar of ihatelag
ihatelag asked on
Microsoft SQL ServerSQL
4 Comments1 Solution209 ViewsLast Modified:
Hi there,

I have a query which selects the balance and Credit from table called tbl_CustAccount and the customer details, like what currency and if he is a test user is taken from table called tbl_Customer

Because the balance and credit are in difference currencies I need to convert them in to another currency. which is taken from a daily updated table callled tbl_Currency.  

I need to use the last date (latest date) in the list and divide the gbpratio against the balance and the credit.

Please take at the code snippet for the basic premise of the query.

-tables involved:
tbl_CustAccount - contains idcust, balance, credit
tbl_Customer - contains idcust, testuser, currency
tbl_Currency - contains idexchange, currency, gbpratio, date


I think I need to find the max (tbl_Currency.Date) but I can't seem to make it work...your help is most appreciated.!


-- query without conversion
 
SELECT     SUM(tbl_CustAccount.Balance) as Balance, SUM(tbl_CustAccount.Credit) AS Credit
FROM         tbl_CustAccount WITH (nolock) INNER JOIN
                      tbl_Customer WITH (nolock) ON tbl_CustAccount.IDCust = tbl_Customer.IDCust
WHERE     (tbl_CustAccount.IDCust NOT IN (12345, 54321)) AND (tbl_Customer.TestUser = 0)
 
-- the formula i need to use, but I cant get the latest date gbpratio
 
Select sum(tbl_CustAccount.Balance / gbpratio) as Balance,
SUM(tbl_CustAccount.Credit / gbpratio) as Credit
FROM         tbl_CustAccount WITH (nolock) INNER JOIN
                      tbl_Customer WITH (nolock) ON tbl_CustAccount.IDCust = tbl_Customer.IDCust
inner join tbl_Currency with (nolock) on tbl_Customer.Currency = tbl_Currency.Currency
WHERE     (tbl_CustAccount.IDCust NOT IN (12345, 54321)) AND (tbl_Customer.TestUser = 0)
group by gbpratio
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros