Thank you very much. This has really resolved the problem

Sorry if I was not clear earlier.

This is the entire process

BEGIN TRY

BEGIN TRANSACTION [Tran1]

select top 1 @caid=ca.id from Cases ca

where ca.applicationstatusentityID in (1,2,12,15)

Insert into CaseAssigned table the caseId selected above

Delete...

I have multiple inserts like inserting the audit_trail table before I insert this caseID into a separate table. So it cannot be a single select insert statement

Can I lock this...

Thank you very much.

It worked really well.

Thanks once again

IDParent IDFlagLevel

10 11

21 02

32 03

40 ...

Thanks. Even this does not give the correct output.

Because for "A", the Trend again changes on the 9th. and this is not reflected in the output

Thanks for the reply. However this fails with the following data

DECLARE @tbl TABLE (Name Varchar(10), Date Datetime, Trend varchar(10), Strength varchar(10))

insert into @tbl values('A', '01-Jul-2011', 'Buy', 'Mid')

insert into @tbl values('A',...