Question

我有2个表格:数字和事实。

缩略语:

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

在《事实》中,许多账户只有B类。我需要获得S类账户的借项和贷项摘要。

实例数据的解决办法实例:

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.

因此,如何用时Key和SitKey获得S账户借记和Cred Sum?

Answer 1

基本上,你可以拿到。

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

Answer 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

Answer 3

或许可以这样说:

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

友情链接