April 11, 2018 at 8:55 am
my table is simple it has 1,000's of rows. one row is the account balance in USD, and the other row (and they are not sequential) in local currency. In this example I have shown a single Canadian balance
ENT_1018 ACC_3030000 USD USD 500.00
ENT_1018 ACC_3030000 CAD CAD 515.00
I need one row where the USD is in column 5 and the CAD amount in column 6. And as shown the local currency id in column 3 and the USD currency ID in column 4.
ENT_1018 ACC_3030000 CAD USD 500.00 515.00
I did this
Select entity,account,localcurrencyID, 'USD' AS reportcurrencyID, Amt
from dbo.CIMBalances
Group by entity,account,localcurrencyID, reportcurrencyID, Amt
this did not work as I still get two lines. I understand it is because the localcurrencyID is different. But I cannot figure out how to get around this.
April 11, 2018 at 9:07 am
SELECT
Entity
, Account
, MAX(CASE
WHEN LocalCurrencyID = 'USD' THEN Amt
ELSE 0
END) AS USDAmount
, MAX(CASE
WHEN LocalCurrencyID = 'CAD' THEN Amt
ELSE 0
END) AS CADAmount
FROM dbo.CIMBalances
GROUP BY
Entity
, Account
John
April 11, 2018 at 9:14 am
sorry was not clear, CAD was just my example there are 1,000's of rows with many different local currencies. And we get new currencies usually quarterly. thanks
CAD
MXN
TWD
BUR
PLN
etc. etc.
April 11, 2018 at 9:25 am
Seems like you need to do some Dynamic PIVOT work
(Sorry, don't have links to Jeff's articles on hand)
April 11, 2018 at 9:39 am
Where do all the currencies come from? This page lists only 178, so you could use that as your basis, extend the query I posted earlier, and get a result set with 180-odd columns. If you need to narrow that down, you can use your front end to display only the columns you're interested in.
John
April 11, 2018 at 10:09 am
Not sure, but give this a try:SELECT L.Entity, L.Account,
R.Local_Currency_ID,
L.Report_Currency_ID,
L.Amt,
R.Amt
FROM (SELECT U.Entity, U.Account, U.Currency_ID AS Report_Currency_ID, R.Amt
FROM Your_Table AS U
WHERE Currency_ID = 'USD') AS L
INNER JOIN (SELECT C.Entity, C.Account, C.Currency_ID AS Local_Currency_ID, R.Amt
FROM Your_Table AS C
WHERE Currency_ID <> 'USD') AS R
ON L.Entity = R.Entity
AND L.Account = R.Account
;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
April 11, 2018 at 1:18 pm
ok so I got this to work just fine, and it returns the data set I need. Except for the last column Sum([GLReportingBalance(Local Currency)]) AS [GLReportingBalance(Local Currency)]
This works fine for all columns except last column
SELECT Entity,
Account,
[Group1/CO/Division],
[Group2/Sub-Account/],
[Group3/sub account],
[Group4/sub account],
KEY7,
KEY8,
KEY9,
KEY10,
AccountDescription,
AccountReference,
FinancialStatement,
AccountType,
ActiveAccount,
ActivityInPeriod,
'' AS AlternateCurrency,
'USD' AS AccountCurrency,
PeriodEndDate,
SUM([GLReportingBalance(USD)]) AS [GLReportingBalance(USD)],
'' AS [,GLAlternateBalance],
Sum([GLReportingBalance(LocalCurrency)]) AS [GLReportingBalance(LocalCurrency)]
FROM dbo.CIMBalancesUnion
GROUP BY Entity, Account, [Group 1/CO/Division], [Group 2/Sub-Account/], [Group 3/sub account], [Group 4/sub account], KEY7, KEY8, KEY9, KEY10, AccountDescription,
AccountReference, FinancialStatement, AccountType, ActiveAccount, ActivityInPeriod, PeriodEndDate
so for the last column I did this
Case
When [AlternateCurrency] ='USD Total'
THEN SUM([GLReportingBalance(USD)])
ELSE Sum([GLReportingBalance(LocalCurrency)])
END
As [GLReportingBalance(LocalCurrency)]
When I do that I get this error message
Msg 8120, Level 16, State 1, Line 23
Column 'dbo.CIMBalancesUnion.AlternateCurrency' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
any suggestions?
April 11, 2018 at 1:23 pm
Add [AlternateCurrency] to the GROUP BY
April 12, 2018 at 1:52 pm
thanks for the reply. but that is actually the issue I am trying to solve. if I use alternate currency as a group function member I get two rows. in my first post I note I need one row. in my last post I note the answer set is correct as it shows one row. but the last column does not have the correct value. so I added the CASE statement thinking/hoping it would not require alternatecurrency in the group statement.
for now there does not seem to be a way around this. someone posted about dynamic pivot and I am reading up on that.
April 13, 2018 at 2:41 am
We're getting to the stage where we need some table DDL (CREATE TABLE statement(s)) and sample data (INSERT statements) if we're going to be able to provide any more help. I still stand by my original suggestion of writing a big query with a MAX(CASE...) expression for every conceivable currency. If that's not practical, by all means look at using dynamic SQL. In my experience, it can get a bit messy, but once you get it working, you may find that it's exactly what you need.
As for getting your query to work, try changing that CASE expression like this:SUM (
CASE
WHEN [AlternateCurrency] ='USD Total' THEN [GLReportingBalance(USD)]
ELSE [GLReportingBalance(LocalCurrency)]
END
) AS [GLReportingBalance(LocalCurrency)]
John
April 13, 2018 at 8:12 am
agreed let me put together the necessary table data and post. will do that today, or maybe over the weekend thanks
April 13, 2018 at 8:58 am
thanks that was so obvious, that I missed the "tree" for the forest. I just need to sum that section same as I was doing. when I replaced the simple one line with the new CASE statement I should have kept the Sum function. duhh, thanks
April 16, 2018 at 2:48 pm
ok sorry, had an unexpected family matter come up over the weekend, so I am later getting data loaded.. attached is an excel workbook, with two worksheets
answerset = what I need the final view to look like
sourcedata = the raw table data.
Let me know if you need additional help. so far I have not found a solution.
April 17, 2018 at 1:15 am
I thought your query worked, except for the last column, which I fixed for you?
This query should work for your sample data and expected results. Note that it's not tested, since you didn't supply CREATE TABLE and INSERT statements as I requested.
SELECT
Entity
, Account
, MAX(CASE
WHEN LocalCurrencyID = 'USD' THEN Amt
ELSE 0
END) AS USDAmount
, MAX(CASE
WHEN LocalCurrencyID <> 'USD' THEN Amt
ELSE 0
END) AS LocalAmount
FROM dbo.CIMBalances
GROUP BY
Entity
, Account
John
April 17, 2018 at 7:58 am
you are correct, I did forget that. you see I am not a developer as I suspect many on this board are. most of my projects are proof of concept, then I write the specifications up and turn it over to a developer. so I actually do not know "create a table" command, I just copy an existing table and modify accordingly. I use MS Access connected to my table to load data. but I had a problem similar to this about a year ago, and you are correct they asked for create table and insert data statements and I figured it out. Hold on I will do that again.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply