How to sum Accounts

Question

I have 3 tables: DimAccounts, DimTime and FactBudget.

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

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

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

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

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   [Interlux].[dbo].[FactBudget] 
GROUP  BY [TimeKey], 
          [AccountKey] 

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

Solution example:

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

In Fact budget there is no Account with type S!!!! we need to get it (example 1.11000 just for date 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

But I need each S Account Debit and credit Sum by date.

Answer 1

SELECT a.TimeKey, a.AccountKey, SUM(a.Debit) AS 'DebitSum', SUM(a.Credit) AS 'CreditSum'
FROM FactBudget a
JOIN DimAccounts b on b.AccountKey = a.AccountKey
WHERE b.AccountType='S'
GROUP BY a.AccountKey, a.TimeKey

Is this what you're looking for?

Answer 2

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