It is not possible, as the value "Avis" does not exist in the sample data

--=== Logic breakdown ================================================
--===   -- Group the consecutive enrollments for each waiver
--=== SELECT *, Grouper = DATEADD(DAY,0-DurationRuningTotal,TERMDATE)
--=== FROM (
--===   -- Get the enrollment...

Thanks all,

that's going to take me some time to analyse. The source data is just terrible. They have been swapping the format of the dates on almost a daily...

Thanks for that, guys!  That does work for getting the results I need from that part of my query.  I'm joining these results into a larger data set via...

So, I spent some time creating a function that will successfully convert *MOST* string formats to DATETIME.  This has only been tested in a us-en environment.

CREATE FUNCTION...

Take a look at this post where a present a solution for a similar issue.

You can take this a step further, and create a iTVF for future use

That SQL does not make sense.

Kindly provide DDL scripts and sample data so that we can understand what you are seeing.

This looks like you are simply repeating the same query over and over

SELECT top(1) PM_DataContabileBKI
FROM [xxx].[xxx].[xxx]
order by 1 desc

and subtracting a number of months from the single...