I have a data like this

Where client_id is client's id, code is a status, date1 is a date of begining, date2 is a date of ending, and t is difference between the dates. And the data ORDER BY date1

I'd like to SUM(T) by client_id and code and I get two sums but I want to get three pieces

1. 2957 1029 for first dates
2. 2957 1051 for second dates
3. 2957 1029 for 3d dates

I suppose that I have to make newid like

client_id   code    date1                  date2                 t     newid 
2957    1029    2000-01-01 00:00:00.000 2000-03-01 00:00:00.000 60      1
2957    1029    2000-03-01 00:00:00.000 2000-07-01 00:00:00.000 122     1
2957    1029    2000-07-01 00:00:00.000 2001-01-01 00:00:00.000 184     1
2957    1051    2001-01-01 00:00:00.000 2001-03-01 00:00:00.000 59      2
2957    1051    2001-03-01 00:00:00.000 2001-12-01 00:00:00.000 275     2
2957    1051    2001-12-01 00:00:00.000 2002-06-03 00:00:00.000 184     2
2957    1029    2002-06-03 00:00:00.000 2003-03-01 00:00:00.000 271     3
2957    1029    2003-03-01 00:00:00.000 2004-02-01 00:00:00.000 337     3
2957    1029    2004-02-01 00:00:00.000 2004-08-01 00:00:00.000 182     3
2957    1029    2004-08-01 00:00:00.000 2004-12-01 00:00:00.000 122     3

so that id is determined by id, code and date order id get newid when he change code, The client changes the code over time, the code can take on the same value after a while, but I would like the client to receive a new unique code in this case But I do not know how to make it.

CodePudding user response：

You may try the following using LAG window function to check whenever the code is changed while the date1 is increasing:

SELECT client_id,code, date2, t,
       SUM(flag) OVER (PARTITION BY client_id ORDER BY date1) newid
  FROM
  (
    SELECT *,
           CASE WHEN CODE <> LAG(code, 1, 0) OVER (PARTITION BY client_id ORDER BY date1) 
                THEN 1 ELSE 0 
           END AS flag
   FROM table_name
  ) T

See a demo.

This query supposes there is no code value = 0 in the table. You may replace the 0 inLAG(code, 1, 0) OVER (PARTITION BY client_id ORDER BY date1) with any other value that is not existed in the table, i.e. -1.