問題描述

我有 3 個表:DimAccounts、DimTime 和 FactBudget.

I have 3 tables: DimAccounts, DimTime and FactBudget.

DimAccounts 示例:

DimAccounts example:

AccountKey  Accouncode  AccountType   AccountFrom   AccountTo 
1.10001     10001       S                  11401    27601
1.10002     10002       S                  11401    16501
1.11000     11000       S                  11401    11508
1.110001    110001      B                   NULL    NULL
1.110002    110002      B                   NULL    NULL
1.11400     11400       S                  11401    11408

DimTime 示例:

DimTime example:

   TimeKey      FullDate
    20020102    2002-01-02
    20020103    2002-01-03
    20020104    2002-01-04

FactBudget 示例:

FactBudget example:

TimeKey     AccountKey              Debit    Credit
20080523    1.110002                0.00    884.00
20080523    1.110001                0.00    4251.96
20100523    1.100002                229.40  0.00
20080523    1.100002                711.79  0.00
20090523    1.110002                0.00    711.79
20080523    1.110001                0.00    229.40
20040523    1.100002                0.00    15619.05

事實上，Budget 有很多賬戶只有類型 B.我需要計算賬戶類型為 S(總和)的借方和貸方金額.AccountFrom 和 AccountTo 列顯示 B 類帳戶從哪里開始求和 (AccountFrom) 和哪里結束 (AccountTo).

In FactBudget are many Accounts just with type B. I need to calculate Debit and Credit Sums where Account type is S (Sum). Columns AccountFrom and AccountTo shows B Type Accounts from where to begin summing (AccountFrom ) and where end (AccountTo).

我已經使用 Cursors 制定了解決方案......但是你知道這很糟糕:) 我認為可以以某種方式對 FactBudget 中的數據進行分組(因為在 factbudget 中還有很多列和行 600k)以及在搜索解決方案時(當我只剩下 60k 行):

I have made solution using Cursors.... buth you know this is very bad :) I think there somehow to Group data in FactBudget (because there also many columns in factbudget and rows 600k) and when search for solution (when I group left just 60k rows):

SELECT [TimeKey], 
       [AccountKey], 
       SUM([Debit]), 
       SUM([Credit]) 
FROM   [dbo].[FactBudget] 
GROUP  BY [TimeKey], 
          [AccountKey] 

那么，如何通過TimeKey和AccountKey獲取S賬戶的借記和貸記金額?(AccountKey 數據類型為 nvarchar)

So, How to get S Accounts Debit and Cred Sum by TimeKey and AccountKey? (AccountKey datatype is nvarchar)

解決方案示例:

TimeKey     AccountKey  Debit    Credit
20080523    1.10002     0.00    2500
20080523    1.11000     0.00    8000
20080524    1.10002     900  0.00

實際上預算中沒有類型為 S 的帳戶！！！！我們需要得到它(例如 1.11000 僅適用于日期 20080523):

select 
SUM(Debit), SUM(Credit)
from FactBudget
LEFT JOIN [DimAccounts]
ON [DimAccounts].[AccountKey] = FactBudget.[AccountKey]   
where CAST([DimAccounts].AccountCode AS INT) >=11401    
and CAST([DimAccounts].AccountCode AS INT) <= 11508
and FactBudget.Timekey = 20080523

但我需要按日期顯示每個 S 帳戶的借記和貸記金額.

推薦答案

據我所知，您需要將 DimAccounts 加入到自身中，才能將 B 類帳戶與其對應的 S 類帳戶關聯起來帳戶，然后加入設置為 FactBudget 的那一行以最終獲得數字.像這樣:

As far as I can see, you need to join DimAccounts to itself to associate B-type accounts with their corresponding S-type accounts, then join that row set to FactBudget to finally obtain the figures. Something like this:

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 BETWEEN s.AccountFrom AND s.AccountTo
  INNER JOIN FactBudget  f ON f.AccountKey = b.AccountKey
WHERE s.AccountType = 'S'
  AND b.AccountType = 'B'
GROUP BY
  f.TimeKey,
  s.AccountKey

這篇關于如何總結賬戶的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！