I want to create a new column, in this case "Reiterate" that marks "YES" for the rows that have the same key (Clave) and have less than 7 days between `resolutiondate` and `createddate`, and the column `Massive` have to be "NO". Here is an example of what I want to get.

![enter image description here](https://i.stack.imgur.com/0lUrQ.png)

I want to try using `lead and lag` formula to sort the dates from lowest to highest, but don't know how to apply it in SQL. Im not really sure if it is possible to do get that column.

Any help is really appreciated. Thanks a lot

Edit.

I used the formula that you suggest and it works! , thanks again for all the help :)

```
SELECT top 100
CF1.STRINGVALUE AS 'ID OT',
jis.created, 
jis.resolutiondate,
IIF(CF3.STRINGVALUE like 'IDR-%','SI','NO') AS 'Massive',
cfo8.customvalue AS 'Solved',
CASE WHEN jis.resolutiondate IS NULL 
     THEN 'NO'
     WHEN LAG(resolutiondate) OVER (PARTITION BY 'ID OT' ORDER BY resolutiondate) >  DATEADD(DAY, -7, resolutiondate)
     AND LAG(CF3.STRINGVALUE) OVER (PARTITION BY 'ID OT' ORDER BY resolutiondate) = 'NO'
     AND LAG(cfo8.customvalue) OVER (PARTITION BY 'ID OT' ORDER BY resolutiondate) = 'NO'
     THEN 'NO'
     ELSE 'YES'
END AS SLA

FROM [DWH].[JIR].[jiraissue] jis
LEFT JOIN [DWH].[JIR].[customfieldvalue] CF1 ON (CF1.issue = jis.id AND CF1.CUSTOMFIELD = 10004)
LEFT JOIN [DWH].[JIR].[customfieldvalue] CF3 ON (CF3.issue = jis.id AND CF3.CUSTOMFIELD = 10032)
LEFT JOIN [DWH].[JIR].[customfieldvalue] CF14 ON (CF14.issue = jis.id AND CF14.CUSTOMFIELD = 10906)
LEFT JOIN [DWH].[JIR].customfieldoption cfo8 ON (CF14.customfield = cfo8.customfield AND CF14.stringvalue=CAST(cfo8.id AS CHAR))

```