Update financial year end periods

select iYearID,* from _etblPeriod
select * from _etblPeriodYear


select min(txdate) from PostGL



--SELECT * INTO _etblPeriod_20240118
--    FROM _etblPeriod

--update _etblPeriod set bBlocked = 1 where idPeriod > 180

/*Steps:
1. Update first period.
2. Recalculate subsequent period end dates.
3. Update year values. (start dates, and possibly names)*/
--update _etblPeriod set dPeriodDate = '2009-04-30 00:00:00.000' where idPeriod = 1

declare @startPeriod date = '2010-01-31'
--update p
--    set p.dPeriodDate = t.newEndDate
--from _etblPeriod p 
--INNER JOIN(
--select 
--    newEndDate = EOMONTH(@startPeriod,idPeriod-1)
--    ,
--    * from _etblPeriod) t ON p.idPeriod = t.idPeriod

/*year:
    change: dYearStartDate, cYearDescription. */
--select * into _etblPeriodYear_20240118  from _etblPeriodYear

--update y
--    set y.dYearStartDate = t.YearStart
--        ,y.cYearDescription = CONCAT('FY ',FORMAT(t.YearStart,'yyyy' ))
--from _etblPeriodYear y

--inner join (SELECT p.iYearID, DATEADD(day,1,EOMONTH(DATEADD(m,-1,MIN(p.dPeriodDate)),0)) as YearStart 
--    FROM _etblPeriod p GROUP BY p.iYearID ) t on t.iYearID = y.idYear

/*align blocking with previous setting.*/
--update p
--    set p.bBlocked = p2.bBlocked
----select p2.dPeriodDate,p2.bBlocked,* 
--from _etblPeriod p 
--    LEFT JOIN _etblPeriod_20240118 p2 ON p.dPeriodDate = p2.dperioddate
--    where p2.idPeriod is not null
select * from _etblPeriod_20240118 

update p2 set p2.Period = P.idperiod
--select top 100 p2.period,P.idPeriod,* 
from PostGL p2 
    left join (SELECT P1.idPeriod
                        ,COALESCE(DATEADD(DAY,1,P2.dPeriodDate),(SELECT TOP 1 dCommencementDate FROM Entities E WHERE dCommencementDate IS NOT NULL ORDER BY idEntities DESC)) as FromDate
                        ,P1.dPeriodDate as EndDate
                        ,P1.bBlocked
                    FROM _etblPeriod P1 LEFT JOIN _etblPeriod P2 ON P1.idPeriod -1 = P2.idPeriod
                ) P ON CAST(p2.TxDate as date) BETWEEN P.FromDate AND P.EndDate 
                where P.idPeriod IS NOT NULL