How to sum Accounts by account code length?

问题:

I have 2 tables: DimAccounts and FactBudget.

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

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

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.

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.

So, How to get S Accounts Debit and Cred Sum by TimeKey and AccountKey?

回答1:

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

回答2:

SELECT      F.TimeKey,
            D.AccountKey,
            SUM(F.Debit) Debit,
            SUM(F.Credit) Credit
FROM        DimAccounts D
INNER JOIN  FactBudget F ON F.AccountKey LIKE D.AccountKey + '%'
WHERE       D.AccountType = 'S'
GROUP BY    F.TimeKey,
            D.AccountKey

回答3:

Maybe something like this:

SELECT
    FactBudget.TimeKey,
    DimAccounts.AccountKey,
    SUM(FactBudget.Debit) AS Debit,
    SUM(FactBudget.Credit) AS Credit
FROM
    DimAccounts
    JOIN FactBudget
        ON DimAccounts.AccountKey=
           SUBSTRING(FactBudget.AccountKey,0,DATALENGTH(DimAccounts.AccountKey)+1)
WHERE
    DimAccounts.AccountType='S'
GROUP BY
    FactBudget.TimeKey,
    DimAccounts.AccountKey