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

Postgresql – Most recent status of each item as of the 1st of each month

I have a table that is structured in the following way: fiddle

create table test(id,status,datechange)as values
 ('011AVN', 11, '2024-06-21 08:27:13'::timestamp)
,('011AVN', 12, '2024-06-21 08:28:16')
,('011AVN', 21, '2024-07-04 15:01:21')
,('011AVN', 22, '2024-07-07 20:30:30')
,('011AVN', 31, '2024-07-11 17:38:01')
,('011AVN', 32, '2024-07-12 20:30:15')
,('011AVN', 33, '2024-07-31 20:58:54')
,('011AVN', 22, '2024-10-16 16:13:33')
,('011AVN', 22, '2024-12-02 18:03:19')
,('011AVN', 31, '2024-12-10 21:53:04')
,('011AVN', 32, '2024-12-11 22:04:26')
,('011AVN', 33, '2025-01-03 10:51:54');

I need to know what the status was on the first of each month.
For instance:

  • on 2024-07-01, the status was 12,
  • on 2024-08-01 the status was 33

I have been trying to join it with a series generated this way:

select generate_series('2024-07-01', now(), interval '1 month') as s;

But I’m a bit stuck.

Tags:

3

Answers


  1. 0

    How about

    SELECT S.d,
       (SELECT tab.status
        FROM tab
        WHERE tab.datechange <= s.d
        ORDER BY tab.datechange DESC
        LIMIT 1)
FROM generate_series('2024-07-01', now(), interval '1 month') as s(d);

    An index on datechange should make this query perform well.

    Login or Signup to reply.
  2. 0

    (my answer elaborates on Laurenz Albe’s one, who posted first and deserves the credits; it adds distinct by-id timelines)

    -- Each id gets its own timeline, starting with the debuting month of the first measured status: 
with timelines as
(
    select id, generate_series(date_trunc('month', min(dateChange)), now(), interval '1 month') d
    from tab
    group by id
)
-- Then for each month, lookup the last value it had before (well, the first value it had going backwards):
SELECT S.d,
       (SELECT tab.status
        FROM tab
        WHERE tab.id = s.id and tab.datechange <= s.d
        ORDER BY tab.datechange DESC
        LIMIT 1)
from timelines S;

    Here is an SQLFiddle to demo it.

    Login or Signup to reply.
  3. 0

    See example

    with cte as(
  select * 
    ,lead(dateChange,1,date_trunc('month',dateChange)+ interval '1 month')
          over(partition by id order by dateChange) nextChange
  from test
)
select ids.id, reportdt, status, datechange, nextchange ,firstDt
from generate_series(cast('2024-07-01' as date),now(),interval '1 month') reportDt
left join (select id,min(dateChange) firstDt 
            from test 
            where dateChange>='2024-01-07'
            group by id
      )ids on firstDt<=reportDt
left join cte c on c.id=ids.id 
  and c.datechange<=reportDt and c.nextChange>reportDt 
order by ids.id, reportDt
  ;
    id reportdt status datechange nextchange firstdt
    011AVN 2024-07-01 00:00:00+00 12 2024-06-21 08:28:16 2024-07-04 15:01:21 2024-06-21 08:27:13
    011AVN 2024-08-01 00:00:00+00 33 2024-07-31 20:58:54 2024-10-16 16:13:33 2024-06-21 08:27:13
    011AVN 2024-09-01 00:00:00+00 33 2024-07-31 20:58:54 2024-10-16 16:13:33 2024-06-21 08:27:13
    011AVN 2024-10-01 00:00:00+00 33 2024-07-31 20:58:54 2024-10-16 16:13:33 2024-06-21 08:27:13
    011AVN 2024-11-01 00:00:00+00 22 2024-10-16 16:13:33 2024-12-02 18:03:19 2024-06-21 08:27:13
    011AVN 2024-12-01 00:00:00+00 22 2024-10-16 16:13:33 2024-12-02 18:03:19 2024-06-21 08:27:13
    011AVN 2025-01-01 00:00:00+00 32 2024-12-11 22:04:26 2025-01-03 10:53:55 2024-06-21 08:27:13

    fiddle

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

Back To Top
Search