Use date (-1 day) on next row to be the end date for current row-CodePudding

I am trying to self join in my current script in order to find the next row and then whatever day specified it should minus 1 day from it and put that in the end date column for the current row, but I seem to be going wrong somewhere.

SELECT 
    BCG.BudgetId
    ,B.CustomerId
    ,CAST(BCG.StartOfPeriod AS DATE) AS StartOfPeriod
    ,BCG2.EndOfPeriod
    ,ROUND(SUM(BCG.Charge),2) AS ExpenditureBudget
    ,ROUND(SUM(BCG.Consumption),2) AS ConsumptionBudget
    ,ROW_NUMBER() OVER (PARTITION BY BCG.BudgetId ORDER BY BCG.StartOfPeriod ASC) AS rowNum
    ,B.Status
FROM Budgets_BudgetCalcGroup BCG 
INNER JOIN Budgets_Budget B ON B.Id = BCG.BudgetId
LEFT JOIN Budgets_BudgetCalcGroup BCG2 ON 
        BCG2.EndOfPeriod = (SELECT MIN(StartOfPeriod)-1
                            FROM Budgets_BudgetCalcGroup AS t3
                            WHERE t3.StartOfPeriod > t1.StartOfPeriod
                            )
WHERE B.Status = 2 
GROUP BY BCG.BudgetId,StartOfPeriod

Error Received:

Unknown Column BCG2.EndOfPeriod in field list

Expected Output:

254 41  2018-09-01   2018-09-30  29017.8    542331.59   1   2
254 41  2018-10-01   2018-10-31  27858.82   575545.97   2   2
254 41  2018-11-01   2018-11-30  28927.71   576106.15   3   2
254 41  2018-12-01   NULL        34639.71   613779.57   4   2

CodePudding user response：

I found an alternative way other than doing a self join which utilises the LEAD() function.

DATE_ADD(CAST(LEAD(BCG.StartOfPeriod, 1) OVER (PARTITION BY BCG.BudgetId ORDER BY BCG.StartOfPeriod) AS DATE),INTERVAL -1 DAY) AS EndOfPeriod

Output:

254 41  2018-09-01  2018-09-30  29017.8     542331.59   1
254 41  2018-10-01  2018-10-31  27858.82    575545.97   2
254 41  2018-11-01  2018-11-30  28927.71    576106.15   3
254 41  2018-12-01  2018-12-31  34639.71    613779.57   4