Thanks very much!!! That was a lot simpler than I thought. Appreciate the help!!!

Hi Chris,

I've thought over the problem and I've come up with a much simpler solution. I really was overcomplicating the issue. I need to ignore ClaQuestionView altogether.

If we focus on...

Hi Chris,

Sorry that must have been an error on my part but the data does exist in the tables. Any ClaCaseID in the #ClaCases exists in the #ClaQuestionView.

I'll run the...

Hi Chris, any feedback on the problem? Thanks

Works great! Thanks for the alternative method:-)

There are 0's in the data set when no data was found. The two distinct character lengths are 8 and 1

@ VIG: Received error message

'Invalid length parameter passed to the LEFT or SUBSTRING function.'

Thank you both for your help and explanations, appreciate it.

Cadavre thanks so much for you help!!! Your code is brilliant and very efficient 😀

If you do have time please can you explain the CROSS APPLY, LEN and also why...

These are 64 cases out of the 42 000 that need to be defaulted to '01-01-1900' as there is no way of finding out what the correct date should have...

Sorry I actually have no idea how to get the DDL. I'm pretty new to SQL. When I look at the table design the column has data type varchar(2000) if...

I think I have found the problem. There are 64 dates that have a problem. These can be defaulted to null. The leading zero disappears when you create the table...

Thanks for the reply:

I receive the error message: 'Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an...

I have joined the #ClaCases and the #ClaQuestionView table via this script:

Select cc.ClaCaseID,cc.NameID,cc.DiscoverDate,cc.QuestionClassID, zz.ClaCaseID,zz.Question,zz.QuestionDate

from #ClaCases cc

LEFT JOIN

(select max(claquestionhistoryid)claquestionhistoryid , ClaCaseID, QuestionClassID, Question, QuestionDate from #ClaQuestionView

where QuestionID =...

@ ChrisM@Home: I'm extremely sorry for the late reply! I actually completely forgot we were busy with this, used your previous code and moved on to the other problems you...