問題描述
我有 2 個表:DimAccounts 和 FactBudget.
I have 2 tables: DimAccounts and FactBudget.
DimAccounts 示例:
DimAccounts example:
AccountKey AccountCode AccountName AccountGroup AccountType
1.6 1 6 1 NN 6 S
1.6 10 6 10 MMM 6 S
1.6 101 6 101 TTT 6 S
1.6 1010 6 1010 IIII 6 B
1.6 1011 6 1011 OOOO 6 B
1.6 1012 6 1012 KKK 6 B
FactBudget 示例:
FactBudget example:
TimeKey AccountKey Debit Credit
20110719 1.6 1010 20.00 5.00
20110719 1.6 1011 15.00 0.00
20110719 1.6 1000 5.00 0.00
20110719 1.6 1012 10.00 5.00
20110719 1.6 1112 10.00 0.00
事實上，Budget 有很多賬戶只有類型 B.我需要獲取賬戶類型 S(總和)的借方和貸方金額.
In FactBudget are many Accounts just with type B. I need to get Debit and Credit Sums for Account type S (Sum).
示例數據的解決方案示例:
Solution example for example data:
TimeKey AccountKey Debit Credit
20110719 1.6 1 60.00 10.00
20110719 1.6 10 50.00 10.00
20110719 1.6 101 45.00 10.00
要計算總賬戶 1.6 101(帶空格的 7 個符號)的借方和貸方，我們需要對所有賬戶進行子串，實際預算最多為 7 個符號(1.6 1012 -> 1.6 101, 1.6 1112 -> 1.6 111, 1.6 1011->1.6 101) 那么它們在哪里相等(1.6 101 = 1.6 101) 按時間鍵和借方和貸方總和進行分組.
To calculate debit and credit for sum account 1.6 101 (7 symbols with whitespace) we need to substring all acounts in factbudget up to 7 symbols (1.6 1012 -> 1.6 101, 1.6 1112 -> 1.6 111, 1.6 1011->1.6 101) and then where are they equal (1.6 101 = 1.6 101) to group by timekey and sum debit and credit.
要計算總賬戶 1.6 1(帶空格的 5 個符號)的借方和貸方，我們需要對所有賬戶進行子串，實際預算最多為 5 個符號(1.6 1012 -> 1.6 1, 1.6 1112 -> 1.6 1, 1.6 1011->1.6 1) 那么它們在哪里相等(1.6 1 = 1.6 1) 按時間鍵分組，借記和貸記總和:) 等等.
To calculate debit and credit for sum account 1.6 1 (5 symbols with whitespace) we need to substring all acounts in factbudget up to 5 symbols (1.6 1012 -> 1.6 1, 1.6 1112 -> 1.6 1, 1.6 1011->1.6 1) and then where are they equal (1.6 1 = 1.6 1) to group by timekey and sum debit and credit:) and so on.
那么，如何通過 TimeKey 和 AccountKey 獲取 S Accounts Debit 和 Cred Sum?
So, How to get S Accounts Debit and Cred Sum by TimeKey and AccountKey?
推薦答案
基本上，你可以采用 this answer 并更改其中一個連接條件:
Basically, you could take this answer and just change one of the join conditions:
SELECT
f.TimeKey,
s.AccountKey,
SUM(f.Debit) AS Debit,
SUM(f.Credit) AS Credit
FROM DimAccounts s
INNER JOIN DimAccounts b ON b.AccountCode LIKE s.AccountCode + '%'
/* alternatively: ON s.AccountCode = LEFT(b.AccountCode, LEN(s.AccountCode)) */
INNER JOIN FactBudget f ON f.AccountKey = b.AccountKey
WHERE s.AccountType = 'S'
AND b.AccountType = 'B'
GROUP BY
f.TimeKey,
s.AccountKey
這篇關于如何按帳戶代碼長度對帳戶求和?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！