Insert a column to differ medic treatment-CodePudding

I have a database with the dates of pacient treatment like this:

pacient_id |       date |    type | finish
         1 | 2021-01-05 | routine |   null
         1 | 2021-01-10 | routine |   null
         1 | 2021-01-22 | routine |   null
         1 | 2021-01-30 | routine |    yes
         1 | 2021-02-04 | routine |   null
         1 | 2021-02-12 | routine |   null
         1 | 2021-02-19 | routine |   null
         1 | 2021-02-22 | routine |    yes

I need a new column on a query that show if the consults are of first treatment or of second, example:

pacient_id |       date |    type | finish | treatment
         1 | 2021-01-05 | routine |   null |         1
         1 | 2021-01-10 | routine |   null |         1
         1 | 2021-01-22 | routine |   null |         1
         1 | 2021-01-30 | routine |    yes |         1
         1 | 2021-02-04 | routine |   null |         2
         1 | 2021-02-12 | routine |   null |         2
         1 | 2021-02-19 | routine |   null |         2
         1 | 2021-02-22 | routine |    yes |         2

When the finish column = yes, then the treatment is finish. The table have many pacients:

pacient_id |       date |    type | finish
         1 | 2021-01-05 | routine |   null
         2 | 2021-01-10 | routine |   null
         5 | 2021-01-22 | routine |   null
         2 | 2021-01-30 | routine |    yes
         1 | 2021-02-04 | routine |   null
         3 | 2021-02-12 | routine |   null
         9 | 2021-02-19 | routine |   null
         1 | 2021-02-22 | routine |    yes

So I order to pacient_id and date. Thanks for help.

CodePudding user response：

Try this :

SELECT pacient_id, date, type, finish
     , 1   count(*) FILTER (WHERE finish = 'yes') OVER (PARTITION BY pacient_id ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS treatment
  FROM your_table

see test result in dbfiddle