Got it Mate. I'll try to redesign this process.

Thanks for your help

Jacob,

Thanks and appreciate your help.

I have tried with eliminating the REPLACE function, but not much helpful.

Reason behind using REPLACE is Some are capturing Test Date time in different format to...

SoHelpMeCodd,

Thanks a bunch for pointing me to the right path. We have seen some additional drivers and have disabled them as a part of trouble shoot. I have been checking...

You made my day...Thankyou so much.

Yes i know the data is really bad...But this data is being extracted from some other multiple source and then it has to be transformed...

@sean,

I appreciate for the attempt.

Apologies as i posted the Wrong SQL. Sorry again...

I Have updated the SQL

The output should have a new column called ENDDate.

EndDate is calculated...

Sample Data posted.. Thanks

@Welsh,

Thanks,

I agree, but thats the environment:-)

There are no open transactions...But when i run DBCC opentran its says, one open transaction is there and the SPID is 66s !

I have never...

Thanks all for you valuable inputs which helped very much and i really appreciate your time and knowledge.

Right now we are rewritting the whole SQL and trying to denormalize the...

Thanks for your inputs J Livingston SQL

Please find the attachments for Execution plan and sample output of INITIAL table.

Initial Table

CREATE TABLE [dbo].[INITIAL](

[TN] [numeric](15, 0) NULL,

[WH] [nvarchar](4) NULL,

[TDDATE] [datetime]...