How to add a counter based on dates?

I have a SQL Server query that yields the following data. IdCx will always have the same FecCx.

For each IdCx, I require to add a chronologically ordered index based on whether FecTest occurred before of after FecCx. If it occurred before, that row number should should increase negatively. If FecTest occurred after FecCx, the row number should increase positively

>Solution :

Seems like you can achieve this with ROW_NUMBER and a (couple) CASE expressions:

SELECT IdCx,
       FecCx,
       FecTest,
       Resultado,
       CASE WHEN FecTest > FecCx THEN ROW_NUMBER() OVER (PARTITION BY IdCx, CASE WHEN FecTest > FecCx THEN 1 END ORDER BY FecTest)
            ELSE ROW_NUMBER() OVER (PARTITION BY IdCx, CASE WHEN FecTest > FecCx THEN 1 END ORDER BY FecTest DESC) * -1 END AS RN
FROM (VALUES(1234,CONVERT(datetime2(0),'2023-01-04 17:00:00'),CONVERT(datetime2(0),'2023-01-02 12:00:00'),0.3),
            (1234,CONVERT(datetime2(0),'2023-01-04 17:00:00'),CONVERT(datetime2(0),'2023-01-03 17:00:00'),0.4),
            (1234,CONVERT(datetime2(0),'2023-01-04 17:00:00'),CONVERT(datetime2(0),'2023-01-04 19:00:00'),0.4),
            (1234,CONVERT(datetime2(0),'2023-01-04 17:00:00'),CONVERT(datetime2(0),'2023-01-04 23:00:00'),0.4),
            (1234,CONVERT(datetime2(0),'2023-01-04 17:00:00'),CONVERT(datetime2(0),'2023-01-05 05:00:00'),0.6))V(IdCx,FecCx,FecTest,Resultado)
ORDER BY IdCx,
         FecTest;