I know that the OP stated that he was OK with NULL and All the same medals.

However, @luis soultion does not take NULL into account.

This should do the trick

            
October 11, 2019 at 5:46 pm
            
#3688107

            
            
                                                
        

A calendar table is definitely the better option.

But, just for fun, I have come up with 2 options that *SHOULD* work with SQL2000.  Unfortunately I do not have SQL2000 to...

the only days i would be exluding are weekend days (so no other holidays to be excluded).

so would a calendar still be the most appropriate way to do this?

I...

Historically, I have found that Stored Procs out-perform functions such as these.

• Try converting the function to a proc
• Try updating stats on the tables being referenced by the function.

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.