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
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
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
create a new post