問題描述

我有 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).

示例數(shù)據(jù)的解決方案示例:

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

這篇關(guān)于如何按帳戶代碼長度對帳戶求和?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！