Join table to itself to get YTD balance

[mSolver]

Hi, I am stuck on a query. Perhaps someone here can shed some light on a possible solution. I have a financial table which contains monthly transactions. I need to create YTD balances from this table. I am doing this by joining the table on itself to that for each month, all transactions are grouped from that month and all previous months. So simply, Jan, Jan+Feb, Jan+Feb+Mar, etc. I need this to be perpetual, so i cant code any date ranges within the query.

I think the issue is that for a month that does not contain transactions using the same "Dimensions" such as the account, the data no longer shows for that month. So if Account 1000 was in Jan but it was not in Feb, then my query drops off Account 1000 in the Feb result.

I have tried changing the join type but that does not seem to resolve it.

Here is a simplified version of the query. trying to keep the post short and to the point.
---------------------------------------------------------
SELECT
t1.AccountID,
t1.AFEID,
t1.CompanyID,
sum(t2.Amount)as AMOUNTMST,
t1.CostCenterID,
t1.CurrencyMstID,
t1.DatasetID,
t1.LegalEntityID,
t1.LocationID,
t1.PeriodID

FROM glsummary176 t1
INNER JOIN glsummary176 t2
ON t1.AccountID=t2.AccountID
AND t1.AFEID=t2.AFEID
AND t1.CompanyID=t2.CompanyID
AND t1.CostCenterID=t2.CostCenterID
AND t1.CurrencyMstID=t2.CurrencyMstID
AND t1.LegalEntityID=t2.LegalEntityID
AND t1.LocationID=t2.LocationID
AND t1.PeriodID >=t2.PeriodID

WHERE Left(t1.AccountID,1) IN (1,2,3)

GROUP BY
t1.AccountID,
t1.AFEID,
t1.CompanyID,
t1.CostCenterID,
t1.CurrencyMstID,
t1.DatasetID,
t1.LegalEntityID,
t1.LocationID,
t1.PeriodID

order by t1.periodid
--------------------------
Thanks in advance for any ideas.

Mike

 

[PDreyer]

You did not post ddl or sample data
Maybe this might help

-- create test data
select * into #gl from ( select 
1,1,1, 105 union all select
1,1,1, 110 union all select
1,1,3, 308 union all select
1,1,4, 401 union all select
2,1,1, 105 union all select
2,1,1, 110 union all select
2,1,3, 308 union all select
2,2,4, 401 union all select
2,1,4, 430)t(AccountID,CompanyID,PeriodID,Amount)
--end create test data

select a.AccountID, a.CompanyID, n.PeriodID, isnull(sum(g.Amount),0)
from 
(select distinct AccountID,CompanyID from #gl)a
cross join 
(select number
 from master..spt_values
 where type='P'
   and number between 1 and 12)n(PeriodID)
left join #gl g
  on g.AccountID=a.AccountID
 and g.CompanyID=a.CompanyID
 and g.PeriodID>=n.PeriodID
 and g.PeriodID<n.PeriodID+1
group by a.AccountID, a.CompanyID, n.PeriodID
order by a.AccountID, a.CompanyID, n.PeriodID

-- cleanup
drop table #gl

 

[drlex]

PDreyer

 ...
 and g.PeriodID>=n.PeriodID
 and g.PeriodID<n.PeriodID+1
 ...

would equate to g.PeriodID = n.PeriodID, which is incorrect for the required calculation.

soi la, soi carré

 

[mSolver]

Thanks for your response PDreyer, not quite what I need. With your example data, I would expect that for each period, the total for all previous periods is returned.

Example:
If each month had 2 transactions, i would expect to see 2 transactions for Jan, 4 transactions for Feb, 6 transactions for Mar, etc.

Also, since this YTD calculation is happening on a financial system (Microsoft AX), the periods span over years starting with 200701 and currently go to 200909, so i dont think i can hard code a period range (otherwise the original query would work).

I will post a better example.
thanks again,
Mike

 

[drlex]

Apologies - hit submit and not preview.
Mike,
PDreyer's general approach of grouping a list of accounts and then joining such to a summation of account values is good. What I am unsure of is whether you need a single YTD total, or whether you want columns showing cumulative totals through the year. I assume that your table is confined to a single year, such that you do not have to distinguish between period 1 last year and period 1 this year.

soi la, soi carré

 

[mSolver]

Thanks drlex, my apologies for not detailing my challange. The result would be a single column amount. The way it currently works is that the ETL which contains this query loads another table. The period that is displayed is the current months period. So in my previous example if you have 2 transacitons in 200901 (Jan 2009) and 2 transactions in 200902, when you look at the table you should see 4 transactions for 200902 (2 from Jan + 2 from Feb). Now to get the YTD total, an additional query will group by the Period. (so in this current query I am really just aggregating records and changing the period column, rather than truely getting the YTD totals.

I think I may have confused the situation even more. It is actually quite simple (Need a whiteboard here).

I will post a sample in a bit.
thx
Mike

 

[drlex]

Mike,
I crossed with your post. I should have followed the standard creed of the forum in suggesting that you post sample data and required output, so as to assist all.

soi la, soi carré

 

[mSolver]

I am taking a different approach to the query just to show what i am trying to accomplish. I included two sample tables, the query will show the correct YTD sum for the first dataset. However, the query does not work for the second data set. The second set is a more realistic example.

The end result is that for every period and other column combinations, i need to be able get the YTD total.

Here is the example. Hope this helps explain what i am trying to do.

/* SAMPLE 1 - correct results
select * into #gl from
( select 1500,10a,100,200901,75.1 union all
select 1500,10a,100,200901,25.21 union all
select 1500,10a,100,200902,125.3 union all
select 1500,10a,100,200902,75.41 union all
select 1500,10a,100,200903,150.5 union all
select 1500,10a,100,200903,150.61 union all
select 1500,10a,100,200904,200.7 union all
select 1500,10a,100,200904,300.81
) t(AccountID,CompanyID,CostCenterID,PeriodID,Amount)
*/

/* SAMPLE 2 - bad results
select * into #gl from
( select 1500,10a,100,200901,75.1 union all
select 1500,10a,101,200901,25.21 union all
select 1500,10a,100,200902,125.3 union all
select 1500,10a,104,200902,75.41 union all
select 1500,10a,100,200903,150.5 union all
select 1500,10a,100,200903,150.61 union all
select 1500,10a,105,200904,200.7 union all
select 1500,10a,103,200904,300.81
) t(AccountID,CompanyID,CostCenterID,PeriodID,Amount)
*/

--drop table #gl

Select periodId,accountId, CompanyId,CostCenterId,isnull((Select sum(#gl.Amount)+sum(t1.Amount)
From #gl t1
Where
t1.accountID=#gl.accountID
and t1.periodID<#gl.periodID),sum(#gl.Amount))
From #gl
Group by periodId,accountId, CompanyId,CostCenterId

Thx again,
Mike