PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Optimize Finding Max Value per Year, String, Attribute

How do I get the rows for each year by scenario that are associated to the latest scenario for each year while being at-most prior month (future budgets & forecast scenarios exist as well)

Fields:

  • scenario (VARCHAR)

    • Distinguishes between budget or forecast
      • Format for forecasts (Jan Fcst, Feb Fcst, …, Dec Fcst)
      • Format for budgets (Jan Adjusted Budget, Feb Adjusted Budget, … Dec Adjusted Budget)
    • Forecast & Budget are updated every month for each month in current year (each unique forecast includes 12 rows (Jan – Dec).

  • a_year (VARCHAR in format 'FY%y') Ex: FY20, FY21, … FY24

    • indicates year the forecast or budget applies to

  • a_month (VARCHAR in format '%b') Ex: Jan, Feb, …, Dec

    • indicates the month the forecast or budget applies to

  • amount (DOUBLE)

+----------------+------+-----+-----+
| Jan Fcst       | FY24 | Jan | 100 |
+----------------+------+-----+-----+
| Jan Fcst       | FY24 | ... | 150 |
+----------------+------+-----+-----+
| Jan Fcst       | FY24 | Dec | 80  |
+----------------+------+-----+-----+
| Feb Fcst       | FY24 | Jan | 110 |
+----------------+------+-----+-----+
| Feb Fcst       | FY24 | ... | 180 |
+----------------+------+-----+-----+
| Feb Fcst       | FY24 | Dec | 103 |
+----------------+------+-----+-----+
| Jan Adj Budget | FY24 | Jan | 120 |
+----------------+------+-----+-----+
| Jan Adj Budget | FY24 | ... | 90  |
+----------------+------+-----+-----+
| Jan Adj Budget | FY24 | Dec | 110 |
+----------------+------+-----+-----+
| Feb Adj Budget | FY24 | Jan | 130 |
+----------------+------+-----+-----+
| Feb Adj Budget | FY24 | ... | 200 |
+----------------+------+-----+-----+
| Feb Adj Budget | FY24 | Dec | 120 |
+----------------+------+-----+-----+

Each historical year has 288 rows per scenario when monthly forecasts/budgets are updated each month. (12 budgets & 12 forecasts, each containing 12 rows.

If the Forecast was last updated in Feb 2024 & Budget in Apr then the output should only include 12 rows in format:

+---------------------+------+-----+----+
| Feb Fcst            | FY24 | Jan | $$ |
+---------------------+------+-----+----+
| Feb Fcst            | FY24 | ... | $$ |
+---------------------+------+-----+----+
| Feb Fcst            | FY24 | Dec | $$ |
+---------------------+------+-----+----+
| Apr Adjusted Budget | FY24 | Jan | $$ |
+---------------------+------+-----+----+
| Apr Adjusted Budget | FY24 | ... | $$ |
+---------------------+------+-----+----+
| Apr Adjusted Budget | FY24 | Dec | $$ |
+---------------------+------+-----+----+
Tags:

2

Answers


  1. 0

    I used a CTE to find the max YearMonth for each scenario type

    WITH CTE AS (
SELECT MAX(
  a_year||RIGHT('00' || CAST(EXTRACT(MONTH FROM to_date(Substring(scenario,1,3), 'Mon')) as VARCHAR(2) ),2)
          ) as MaxYearMonth, 
           Substring(scenario,5,4) as ScenarioType
FROM Example
GROUP BY  Substring(scenario,5,4)
)
SELECT * 
FROM Example a
INNER JOIN CTE on CTE.MaxYearMonth = a.a_year||RIGHT('00' || CAST(EXTRACT(MONTH FROM to_date(Substring(a.scenario,1,3), 'Mon')) as VARCHAR(2) ),2)
  AND CTE.ScenarioType= Substring(a.scenario,5,4)

    fiddle

    Login or Signup to reply.
  2. 0

    This solution uses DISTINCT ON

    WITH CTE AS (
SELECT DISTINCT ON (Substring(scenario,5,4))
  Scenario, a_year||RIGHT('00' || CAST(EXTRACT(MONTH FROM to_date(Substring(scenario,1,3), 'Mon')) as VARCHAR(2) ),2) as MaxYearMonth
FROM EXAMPLE 
ORDER BY Substring(scenario,5,4) DESC, a_year||RIGHT('00' || CAST(EXTRACT(MONTH FROM to_date(Substring(scenario,1,3), 'Mon')) as VARCHAR(2) ),2)  DESC
)
SELECT * 
FROM Example a
INNER JOIN CTE on CTE.MaxYearMonth = a.a_year||RIGHT('00' || CAST(EXTRACT(MONTH FROM to_date(Substring(a.scenario,1,3), 'Mon')) as VARCHAR(2) ),2)
  AND CTE.Scenario= a.scenario

    Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search