如何总结账户
我有 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
相关文章