select t.NOperacion operacion,
t.fecha,
cast(datediff(month,
first_value(t.fecha) over(order by t.NOperacion),
last_value(t.fecha) over(order by t.NOperacion))
as varchar(2)) + ' meses' as meses
from Tabla t
where t.NOperacion = 4548764
The result would be something similar to this:
+-----------+------------+---------+
| operacion | fecha | meses |
+-----------+------------+---------+
| 4548764 | 01/01/2022 | 4 meses |
| 4548764 | 01/02/2022 | 4 meses |
| 4548764 | 01/03/2022 | 4 meses |
| 4548764 | 01/04/2022 | 4 meses |
| 4548764 | 01/05/2022 | 4 meses |
+-----------+------------+---------+
It should be normal for you using the DATEDIFF, however it depends on the configuration of the COLLATE, I did tests with another one different from Latin and SQL Server takes the value 1 as month and therefore the subtraction will always give you 0 (months).
UPDATE:
The thing with using FIRST_VALUEand LAST_VALUEas it is now is that it works fine because of having that WHERE, in case you want to do it for all records, but still have the difference of the first and last date value BY operation , the query would look like this:
select t.NOperacion operacion,
t.fecha,
cast(datediff(month,
first_value(t.fecha) over(partition by t.NOperacion order by t.NOperacion),
last_value(t.fecha) over(partition by t.NOperacion order by t.NOperacion))
as varchar(2)) + ' meses' as meses
from Tabla t
This may help you:
The result would be something similar to this:
It should be normal for you using the DATEDIFF, however it depends on the configuration of the COLLATE, I did tests with another one different from Latin and SQL Server takes the value 1 as month and therefore the subtraction will always give you 0 (months).
UPDATE:
The thing with using FIRST_VALUEand LAST_VALUEas it is now is that it works fine because of having that WHERE, in case you want to do it for all records, but still have the difference of the first and last date value BY operation , the query would look like this: