Datediff

Transact-SQL
1

hello ,

I am working on a piece of code which is treating dyalisis patients treatments.

All of these patients have a starting treatment date and some of them have an ending treatment date. Those who don'thave an ending date is because the are still on treatment so we will take the present date (getdate()).

These could be a example of the database:

START END DIFF IN MONTHS
05/03/2013 05/01/2015 22
28/05/2012 06/06/2017 61
21/08/2016 21/03/2018 19

My question is about how to calculate the difference in months for each year , for example in the first case the 22 months are 9 from 2013 , 12 from 2014 and 1 from 2015.

I need the three results...

Thanks in advance.

2 
with 
 yourSource as (select  cast('20130305' as date) as start_date, cast('20150105' as date) as end_date union all
                   select  cast('20120528' as date) , cast('20170606' as date) union all
                   select  cast('20160821' as date) , cast('20180321' as date)
                  )

select 
   s.start_date
   ,s.end_date
   ,datediff(month,s.start_date,s.end_date) as diff_in_months
from yourSource as s

output:

start_date end_date diff_in_months
05/03/2013 00:00:00 05/01/2015 00:00:00 22
28/05/2012 00:00:00 06/06/2017 00:00:00 61
21/08/2016 00:00:00 21/03/2018 00:00:00 19

dbfiddle here

3

And if you don't have an end date then hopefully the value will be null so just use
coalesce(s.end_date,getdate()) in the above query.

Note that datediff will count the month boundaries between the two dates - so 31 jan - 1 feb is 1 month.
On the 1st of the month you will see all patients still under treatment increasing by a month.