August 9, 2019 at 6:15 pm
so I really struggle with grouping. I have a view and it takes a few accounts and basically uses the same account. when the view runs I get 5 lines. 4 lines for the accounts I am grouping and 1 line for the master account.
so the first four accounts 1560020, 1560100, 1560300, 1560400 all get mapped to 1560000. In my source data each account has a value. When my view executes I get 5 lines. I need one line with all 5 values summed up. I cannot figure out how to do this. Any help is appreciated.
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,
'' 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,
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(Loc
August 9, 2019 at 6:40 pm
Got some sample data? That would help a LOT.
August 9, 2019 at 6:42 pm
I'm afraid that I cannot make out much of your post. It appears that your query has been truncated. Not that I know how much help it would be to see the full query, but we could start there. The post has a button "insert/edit code sample". Use that to include your code to make it a little more readable.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 9, 2019 at 6:47 pm
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,
'' 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,
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
August 9, 2019 at 6:59 pm
sorry I missed the truncation
Raw data
HFMENTITY > account > Amt
ENT_1010 ACC_1560000 15,015,915.00
ENT_1010 ACC_1560020 2,646,01400
ENT_1010 ACC_1560100 4,411,360.69
ENT_1010 ACC_1560400 2,544,500.00
ENT_1010 ACC_1560300 68,223.56
Current output from my view
ENT_1010 ACC_1560000 15,015,915.00
ENT_1010 ACC_1560000 2,646,01400
ENT_1010 ACC_1560000 4,411,360.69
ENT_1010 ACC_1560000 2,544,500.00
ENT_1010 ACC_1560000 68,223.56
What I need
ENT_1010 ACC_1560000 24,686,013.99
August 9, 2019 at 7:01 pm
it will take me a few minutes but I can create the code for a table with the 4,000 rows. If need I will post the table code over the weekend. thanks
August 9, 2019 at 7:08 pm
If I get this correctly (and I did look very closely at it), you need to push your current query into a common table expression (CTE), so that you can group on the aliases you define. You have a bunch of constant columns; I've moved these outside the CTE.
Your query is not very well-formatted, so I may have missed some columns, but this is something you can work from.
; WITH CTE AS (
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,
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 Account, AcccCurrency, PeriodEndDate, SUM[Amt(USD)], SUM([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 Account, AcccCurrency, PeriodEndDate
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 9, 2019 at 8:30 pm
Does your database have some way to identify the hierarchy of accounts? This feels like something that would be better represented by data than code. Then you could group by the defined "master" account instead of the expression.
August 9, 2019 at 8:35 pm
Chris makes an excellent point. If there is no such mechanism, maybe you should add one. All assuming that this particular group is entirely ad hoc.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 10, 2019 at 3:49 pm
First just to be clear I am not a developer. I am just an accountant who knows enough SQL to get myself into these predicaments 🙂 So please be patient with me.
ok so to answer some questions. The raw data comes from application A, and we are basically just reformatting the data to send to Application B.
Application B (not my choice) summarizes some accounts into "buckets"
As in the first set of CASE WHEN, I must take 4 accounts and change the account to ACC_1560000
So in total there are about 1,500 individual accounts. But only 12 are being "rolled into" 3 accounts.
The source application does not have the capability to create master account rollups.
I did think about creating a table with the account to master account relationship and then just do a UNION. But to be honest creating a 1,500 line table to roll up 12 accounts seemed more effort than just writing the case When statements. Plus we do add new accounts (1-3) per month and that maintenance seemed additional work.
but I do like to do things correctly, so if this groups thinks a new table is the correct solution I would do that. I have always received great information from this forum.
I will try the code above on Monday and let you know how that works. Thanks
August 10, 2019 at 6:24 pm
One advantage of having the table is that you keep the data (the account mapping) apart from the logic. But admittedly, if you have to enter new accounts manually by writing INSERT statements, this is not really attractive. It might have been a different thing if there had been a nice slick UI for the task, but obviously there isn't. And you may find it is easier to review the mapping by looking at the query than having to look in the table and then cross-check the query.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 12, 2019 at 1:48 pm
so you did an amazing job interpreting my code 🙂 sorry I am just learning proper coding syntax rules. And Yes you did get all the columns.
tried the above code, this is my error message
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'FROM'.
so I see how you separated the CASE columns from the fixed value columns, that is very interesting
; WITH CTE AS (
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,
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 Account, AcccCurrency, PeriodEndDate, SUM[Amt(USD)], SUM([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 Account, AcccCurrency, PeriodEndDate
And yes basically if the raw data did not have multiple lines, really all I am doing is creating fixed value or blank columns to meet the need of the target application.
August 12, 2019 at 2:01 pm
With sample data, CREATE TABLE etc it is difficult to test.You can't expect to get working queries, but you get something to work from.
Did you try putting a SELECT before the CASE? As for the FROM - check what comes before the FROM.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 12, 2019 at 2:08 pm
First just to be clear I am not a developer. I am just an accountant who knows enough SQL to get myself into these predicaments 🙂 So please be patient with me.
ok so to answer some questions. The raw data comes from application A, and we are basically just reformatting the data to send to Application B.
Application B (not my choice) summarizes some accounts into "buckets"
As in the first set of CASE WHEN, I must take 4 accounts and change the account to ACC_1560000
So in total there are about 1,500 individual accounts. But only 12 are being "rolled into" 3 accounts.
The source application does not have the capability to create master account rollups.
I did think about creating a table with the account to master account relationship and then just do a UNION. But to be honest creating a 1,500 line table to roll up 12 accounts seemed more effort than just writing the case When statements. Plus we do add new accounts (1-3) per month and that maintenance seemed additional work.
but I do like to do things correctly, so if this groups thinks a new table is the correct solution I would do that. I have always received great information from this forum.
I will try the code above on Monday and let you know how that works. Thanks
I would create a mapping table for accounts that roll up into parent/master accounts. It is not necessary to track all accounts. Just the ones that have to be rolled up.
First, create some sample data.
CREATE TABLE #EntityAccount (
Entity varchar(20) NOT NULL
, Account varchar(20) NOT NULL
, Amount decimal(18,2) NOT NULL
);
GO
INSERT INTO #EntityAccount ( Entity, Account, Amount )
VALUES ( 'ENT_1010', 'ACC_1560020', 10.58 )
, ( 'ENT_1010', 'ACC_1560100', 34.33 )
, ( 'ENT_1010', 'ACC_1560300', 17.64 )
, ( 'ENT_1010', 'ACC_1560400', 43.93 )
, ( 'ENT_1010', 'ACC_1690010', 29.21 );
GO
Now create some sample mappings
CREATE TABLE #EntityAccountMap (
Entity varchar(20) NOT NULL
, Account varchar(20) NOT NULL
, MasterAccount varchar(20) NOT NULL
);
GO
INSERT INTO #EntityAccountMap ( Entity, Account, MasterAccount )
VALUES ( 'ENT_1010', 'ACC_1560020', 'ACC_1560000' )
, ( 'ENT_1010', 'ACC_1560100', 'ACC_1560000' );
GO
And query the data with the mappings
-- Individual records, with the MatserAccount
SELECT ea.Entity, Account = ISNULL(eam.MasterAccount, ea.Account), ea.Amount
FROM #EntityAccount AS ea
LEFT JOIN #EntityAccountMap AS eam
ON ea.Entity = eam.Entity
AND ea.Account = eam.Account
-- Aggregate records, with the MatserAccount
SELECT ea.Entity, Account = ISNULL(eam.MasterAccount, ea.Account), Amount = SUM(ea.Amount)
FROM #EntityAccount AS ea
LEFT JOIN #EntityAccountMap AS eam
ON ea.Entity = eam.Entity
AND ea.Account = eam.Account
GROUP BY ea.Entity, ISNULL(eam.MasterAccount, ea.Account);
August 12, 2019 at 2:57 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.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply