SQLTeam.com | Weblogs | Forums

Setting (Updating) Beginning balance

So looking for an update SQL query sequence that will update the Beginning balance to what the previous periods ending balance is.

In the image the above data is what I end up with in a temp table but I need to update it to what is in the second table (highlighted in yellow)

TIA
Stephen

;WITH cte AS
(
	SELECT
		BeginningBalance, 
		LAG(EndingBalance,1,0)  OVER (PARTITION BY companyCode ORDER BY FiscalYear, FiscalPeriod) AS NewBeginningBalance
	FROM
		YourTable
)
UPDATE cte SET BeginningBalance = NewBeginningBalance;

Run the query within the cte to see what the values will be updated to.

Here are the results - top section is what in in the table - below is the inside query - doesn't seem to be giving me what I am looking to update to?

Stephen

Crap! My bad, I always forget that LAG is not recursive. Something like this should work.

;WITH cte AS
(
	SELECT 
		CompanyCode,
		FiscalYear,
		FiscalPeriod,
		SUM(BeginningBalance + DebitAmount + CreditAmount)
			OVER(PARTITION BY CompanyCode ORDER BY FiscalYear, FiscalPeriod) AS NewEndingBalance
	FROM
		YourTable
)
UPDATE c SET
	EndingBalance = NewEndingBalance,
	BeginningBalance = LAG(NewEndingBalance,1,0)
		OVER (PARTITION BY CompanyCode ORDER BY FiscalYear, FiscalPeriod)
FROM
	cte c
	INNER JOIN YourTable y ON
		y.CompanyCode = C.CompanyCode
		AND y.FiscalYear = C.FiscalYear
		AND y.FiscalMonth = C.FiscalMonth;

Feel like I am getting close

;WITH cte AS
(
SELECT
t.CompanyCode,
t.FiscalYear,
t.FiscalPeriod,
SUM(t.BeginningBalance + t.DebitAmount - t.CreditAmount)
OVER(PARTITION BY t.CompanyCode ORDER BY t.FiscalYear, t.FiscalPeriod) AS NewEndingBalance
FROM
##TEMP_BALANCE2 t
)

UPDATE y SET
EndingBalance = NewEndingBalance,
BeginningBalance = LAG(NewEndingBalance,1,0)
OVER (PARTITION BY y.CompanyCode ORDER BY y.FiscalYear, y.FiscalPeriod)
FROM
cte c
INNER JOIN ##TEMP_BALANCE2 y ON
y.CompanyCode = C.CompanyCode
AND y.FiscalYear = C.FiscalYear
AND y.FiscalPeriod = C.FiscalPeriod;

I get the following error

Msg 4108, Level 15, State 1, Line 230
Windowed functions can only appear in the SELECT or ORDER BY clauses.

TIA
Stephen

;WITH cte AS
(
	SELECT 
		CompanyCode,
		FiscalYear,
		FiscalPeriod,
		SUM(BeginningBalance + DebitAmount + CreditAmount)
			OVER(PARTITION BY CompanyCode ORDER BY FiscalYear, FiscalPeriod) AS NewEndingBalance
	FROM
		YourTable
),
cte2 AS
(
	SELECT 
		*, LAG(NewEndingBalance,1,0)
		OVER (PARTITION BY CompanyCode ORDER BY FiscalYear, FiscalPeriod) AS NewBeginningBalance
	FROM
		cte
)
UPDATE y SET
	EndingBalance = NewEndingBalance,
	BeginningBalance = NewBeginningBalance
FROM
	cte2 c
	INNER JOIN YourTable y ON
		y.CompanyCode = C.CompanyCode
		AND y.FiscalYear = C.FiscalYear
		AND y.FiscalMonth = C.FiscalMonth;

This may be more complicated that I need it to be.
reviewing the attached
(In Yellow) Company 400 - Period 01 has an ending balance of -35460601.79, this needs to be that beginning balance of company 400 - Period 02 and then that Period 02 beginning balance is to set as the Period 02 Ending balance - then moving down through Company 400 until you get to a new Company/Fiscalyear.
Company 401 is good and no action is required
Company 500 needs to do what Company 400 did but Company 500 starts at Period 04/05 - filling down until it reaches the next company.

This will process will step the entire table By Year / By Company

I really appreciate your expertise in helping me

Stephen

Try

;With 
cte as
(
select a.* 
from #begBalance a
where a.FiscalPeriod = 1 or a.BeginningBalance <> 0

union all 

select a.CompanyCode , a.FiscalPeriod , a.FisCalYear  , b.EndingBalance ,  a.DebitAmount , a.CreditAmount , cast(b.EndingBalance + a.DebitAmount - a.CreditAmount  as decimal(9,2))
from #begBalance a
,cte b
where a.CompanyCode = b.CompanyCode 
and a.FiscalPeriod = b.FiscalPeriod 
and a.FisCalYear = b.FisCalYear + 1
and a.BeginningBalance = 0
and not exists
	(
	select 1
	from #begBalance c
	where c.CompanyCode = b.CompanyCode 
	and c.FisCalYear = b.FisCalYear 
	and c.CompanyCode = a.CompanyCode 
	and c.FisCalYear = a.FisCalYear 
	)
	
)	


select * 
from cte a
order by a.CompanyCode , a.FiscalPeriod; 

I get this error
Msg 240, Level 16, State 1, Line 212
Types don't match between the anchor and the recursive part in column "EndingBalance" of recursive query "cte".

stephen

updated to decimal(18,2) but I still don't get the results I need -
Stephen

It would be more helpful if you could post what my solution returned versus what you expect -with an an explanation - in a consumable format. This will also help other viewers find a for you.

Thanks

1 Like

Sorry - I hope this helps. These are the results I get from the code provided.

Notice that Company 400 only has Period 01 and Company 500 stops after period 04.

Every Company has all the periods for the year and the ending balance will roll to the next periods beginning balance - resulting in an ending balance of the same since there are no credits/debits.

Thank You
Stephen

This is what I am needing to occur

Every Company has all the periods for the year and the Ending balance for a period will roll to the next periods beginning balance - an with no debits/credits results in the ending balance as well

Sorry it will only let me post one image at a time

Stephen

This is what my TEMP table has it in currently

I am thinking I could get some kind of recursive LAG working that would do it - I can get LAG to work for the first next period but it stops after that.
Stephen

the above means you providing the data in DDL and DML format

So what you will need to do to get help from folks that are busy is the following

create sampleData(CompanyCode varchar(50), FiscalYear int, --etc)

insert into sampleData
select 400, 2019, ---etc

the image you posted would require busy folks to take time out of their busy schedule to do the above DDL and DML. And since they might not have time, they will just ignore your question.

Help us help you

Sorry - I generated a create table script with data - but it says that as a new user I can only attach 2 links?

links? You just need to type the create script with data here. no need for links. we cant copy from an image link anyways

just posting the code from the sql statement I get this

image

create a new post