August 12, 2019 at 3:08 pm
ok I understand that. And I think the end users would like that. the table would give them easier visibility to the rollups.
Let me work on that.
For performance reasons, ensure that you use the same data types in your mapping table as the existing table.
August 12, 2019 at 3:24 pm
all right so lets start over. I actually have a query that limits the raw data. I do not send all the raw information over. We have about 30 Entity codes but I only send over 24. so this the initial view that I think might be a better place to set the new code.
So D.Label is where I get my ACC_xxxxxxx from and C.Label is my ENT_XXXX
I created the table as noted above. dbo.EntityAccountMap
I think I should do the account modification in the first view and then the final view would just be a standard group function.
Am I think correctly? And if I am how could I modify this code to bring in the master account when appropriate?
SELECT A.YearID, B.Label AS PeriodIDValue, B.Description AS PeriodID, C.Label AS Entity, D.Label AS Account, E.Label AS Currency, CAST(A.dData AS Decimal(18, 2)) AS Amt
FROM HYPEA.dbo.CIM_FACT AS A LEFT OUTER JOIN
HYPEA.dbo.CIM_PERIOD AS B ON A.PeriodID = B.ID LEFT OUTER JOIN
HYPEA.dbo.CIM_ICP AS C ON A.EntityID = C.ID LEFT OUTER JOIN
HYPEA.dbo.CIM_ACCOUNT AS D ON A.AccountID = D.ID LEFT OUTER JOIN
HYPEA.dbo.CIM_VALUE AS E ON A.ValueID = E.ID LEFT OUTER JOIN
dbo.cimentity AS F ON C.Label = F.HFMEntityID
WHERE (F.SendToCIM = 'Y')
August 12, 2019 at 3:43 pm
thanks I will check the data types now.
August 12, 2019 at 3:46 pm
so C.Label and d.label are NVARCHAR
So I will make the new table the same.
August 12, 2019 at 4:20 pm
ok so I added the new table and it is now available. but I cannot figure out how to use this new table in the D.Label AS Account
SELECT A.YearID, B.Label AS PeriodIDValue
, B.Description AS PeriodID
, C.Label AS Entity
,D.Label AS Account
,E.Label AS Currency
, CAST(A.dData AS Decimal(18, 2)) AS Amt
FROM HYPEA.dbo.CIM_FACT AS A LEFT OUTER JOIN
HYPEA.dbo.CIM_PERIOD AS B ON A.PeriodID = B.ID LEFT OUTER JOIN
HYPEA.dbo.CIM_ICP AS C ON A.EntityID = C.ID LEFT OUTER JOIN
HYPEA.dbo.CIM_ACCOUNT AS D ON A.AccountID = D.ID LEFT OUTER JOIN
HYPEA.dbo.CIM_VALUE AS E ON A.ValueID = E.ID LEFT OUTER JOIN
dbo.cimentity AS F ON C.Label = F.HFMEntityID LEFT OUTER JOIN
dbo.EntityAccountMap as G ON (C.label = G.Entity and D.label = G.Account)
WHERE (F.SendToCIM = 'Y')
August 12, 2019 at 4:25 pm
I GOT IT, thank everyone
SELECT A.YearID, B.Label AS PeriodIDValue
, B.Description AS PeriodID
, C.Label AS Entity
,Account = ISNULL(G.MasterAccount, D.label)
,E.Label AS Currency
, CAST(A.dData AS Decimal(18, 2)) AS Amt
FROM HYPEA.dbo.CIM_FACT AS A LEFT OUTER JOIN
HYPEA.dbo.CIM_PERIOD AS B ON A.PeriodID = B.ID LEFT OUTER JOIN
HYPEA.dbo.CIM_ICP AS C ON A.EntityID = C.ID LEFT OUTER JOIN
HYPEA.dbo.CIM_ACCOUNT AS D ON A.AccountID = D.ID LEFT OUTER JOIN
HYPEA.dbo.CIM_VALUE AS E ON A.ValueID = E.ID LEFT OUTER JOIN
dbo.cimentity AS F ON C.Label = F.HFMEntityID LEFT OUTER JOIN
dbo.EntityAccountMap as G ON (C.label = G.Entity and D.label = G.Account)
WHERE (F.SendToCIM = 'Y')
August 12, 2019 at 4:51 pm
and with a couple changes this did work, but I think I will go with the table solution. But thanks for all your work.
WITH CTE AS (
Select CASE
WHEN ENTITY + ACCOUNT LIKE 'ENT_1010ACC_1560020' THEN 'ACC_1560000'
WHEN ENTITY + ACCOUNT LIKE 'ENT_1010ACC_1560100' THEN 'ACC_1560000'
WHEN ENTITY + ACCOUNT LIKE 'ENT_1010ACC_1560300' THEN 'ACC_1560000'
WHEN ENTITY + ACCOUNT LIKE 'ENT_1010ACC_1560400' THEN 'ACC_1560000'
WHEN ENTITY + ACCOUNT LIKE 'ENT_1010ACC_1560110' THEN 'ACC_1560010'
WHEN ENTITY + ACCOUNT LIKE 'ENT_1010ACC_1560310' THEN 'ACC_1560010'
WHEN ENTITY + ACCOUNT LIKE 'ENT_1010ACC_1560197' THEN 'ACC_1560097'
WHEN ENTITY + ACCOUNT LIKE 'ENT_1010ACC_1560397' THEN 'ACC_1560097'
WHEN ENTITY + ACCOUNT LIKE 'ENT_1010ACC_1560410' THEN 'ACC_1560097'
WHEN ENTITY + ACCOUNT LIKE 'ENT_1010ACC_1560497' THEN 'ACC_1560097'
WHEN ENTITY + ACCOUNT LIKE 'ENT_1010ACC_1690000' THEN 'ACC_1690097'
WHEN ENTITY + ACCOUNT LIKE 'ENT_1010ACC_1690010' THEN 'ACC_1690097'
ELSE account END AS Account,
Entity AS Entity,
CASE WHEN ENTITY = 'ENT_7620' THEN 'CNY' WHEN ENTITY = 'ENT_7622' THEN 'CNY' ELSE SUBSTRING(Local_Currency_ID, 1, 3) END AS AcctCurrency,
CONVERT(CHAR(10), DATEADD(dd, - 1, DATEADD(mm, 1, PeriodID + DATENAME(yy, GETDATE()))), 101) AS PeriodEndDate, CASE WHEN Substring([account], 5, 1) BETWEEN
'2' AND '4' THEN [USAmt] * - 1 WHEN Substring([account], 5, 1) = 'R' THEN [USAmt] * - 1 ELSE [USAMT] END AS [Amt(USD)], '' AS [Amt(Alt)], CASE WHEN Substring([account], 5,
1) BETWEEN '2' AND '4' THEN [Amt] * - 1 WHEN Substring([account], 5, 1) = 'R' THEN [USAmt] * - 1 ELSE [AMT] END AS [Amt(Local))]
FROM dbo.vw_CIMBalances2
)
SELECT Entity,Account, AcctCurrency, PeriodEndDate, Sum([Amt(USD)]) AS [AMT(USD)], SUM([Amt(Local))]) AS [AMT(Local)],
'' AS Group1, '' AS Group2, '' AS Group3, '' AS Group4, '' AS Key7, '' AS Key8,
'' AS Key9, '' AS Key10, '' AS AcctDesc, '' AS AcctRef, 'C' AS FinancialStatement,
'' AS AcctType, 'TRUE' AS ActiveAcct, 'TRUE' AS ActivityInPeriod, '' AS AlternateCurrency
FROM CTE
GROUP BY Entity,Account, AcctCurrency, PeriodEndDate
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply