How to sum Accounts by account code length?

I have 2 tables: DimAccounts and FactBudget.

DimAccounts example:

  • Ruby on Rails, SQL Server order by and comparison
  • How do I wrap a value in a CDATA tag when creating XML in SQL Server?
  • Using include doesn't change the behavior
  • SQL Server 2016 “FOR JSON” formatting nested JSON
  • EF 6.1 Unique Nullable Index
  • SELECT with a Replace()
  • 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?

    3 Solutions collect form web for “How to sum Accounts by account code length?”

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

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.