Due to the clash of you div names with the web page, I am not recreating the actual xml here.

This should also get what you are looking for.

            
October 8, 2019 at 1:19 pm
            
#3686971

            
            
                                                
        

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...

I don't know your table structures or data types.  I also do not have any sample data to work with.

So the...

You cannot use variables in a view.

Either re-wrtie it as a single statement, or create a stored procedure.

I stand to be corrected, but rumor has it that FULLZ are leaked personal details,...

The correct answer is not available as an option

The following SQL will help you to shred the xml.  You can then work on formatting it as you need.

DECLARE @xmlData xml = '<Data>
  <Monthly_x0020_Income>
 ...

Alternatively, you can rewrite the query to repeat the CASE statement

SELECT DISTINCT 
    ph.STARTDATE
  , ph.SERIALNUMBER
  , dc.DESTINATIONGROUP
  , pi.PLEDGEID
 ...