coalesce function to fill date (Postgresql)

Question

I have a table that looks like this:

    M_DATE    SPEND  M_CLIENT     M_SHOW    ACQ   SP_DATE     A_DATE
    ------    ------   ---- -------- ----------- --------- ---------
    01/02/15  2000     1      GAME       NULL    01/02/15   NULL
    01/03/15  NULL     1      GAME       3       NULL       01/03/15
    01/04/15  NULL     1      GAME       6       NULL       01/04/15
    02/04/15  3000     2      BOOK       NULL    02/04/15   NULL
    02/05/15  NULL     2      BOOK       5       NULL       02/05/15

EDIT: I would like to replace the nulls in the cost_date column with the most recent non-null cost_date for a given ID and type.

Ideally, the resulting table would look like this:

    M_DATE    SPEND  M_CLIENT     M_SHOW    ACQ   SP_DATE     A_DATE
    ------    ------   ---- -------- ----------- --------- ---------
    01/02/15  2000     1      GAME       NULL    01/02/15   NULL
    01/03/15  NULL     1      GAME       3       01/02/15   01/03/15
    01/04/15  NULL     1      GAME       6       01/02/15   01/04/15
    02/04/15  3000     2      BOOK       NULL    02/04/15   NULL
    02/05/15  NULL     2      BOOK       5       02/04/15   02/05/15

I am currently attempting to do a self join and coalesce cost_date on itself. Any ideas?

Answer 1

I used the LEAD function: lead(sp_date)OVER(PARTITION BY m_client, m_TYPE ORDER BY sp_date) AS next_date

The results:

M_DATE    SPEND  M_CLIENT  M_TYPE    ACQ   SP_DATE   next_date
------    ------   ---- -------- ----------- --------- ---------
01/02/15  2000     1      GAME       NULL    01/02/15   02/04/15
01/03/15  NULL     1      GAME       3       NULL         NULL
01/04/15  NULL     1      GAME       6       NULL         NULL
02/04/15  3000     1      GAME       3       02/04/15     NULL
02/05/15  NULL     2      BOOK       5       01/02/15     NULL