如何按帐户代码长度对帐户求和?

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

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

相关文章