Some changes

DECLARE @CYBeginDate smalldatetime, @CYEndDate smalldatetime,

@LYBeginDate smalldatetime, @LYEndDate smalldatetime

select @CYBeginDate = dateadd(year, datepart(year, getdate()) - 1900, '1900-04-01')

select @CYEndDate = dateadd(day, -1, dateadd(month, 12, @CYBeginDate))

select @LYBeginDate = dateadd(year, -1, @CYBeginDate)

select...

Hi

Just a bit simplified version of Nakul Vachhrajani's code

DECLARE @CYBeginDate datetime, @CYEndDate datetime, @LYBeginDate datetime, @LYEndDate datetime

select @CYBeginDate = dateadd(year, datepart(year, getdate()) - 1900, '1900-04-01')

select @CYEndDate = dateadd(day, -1,...

If you want to check the status of SQLAgent

then check whether this query works or not

xp_servicecontrol querystate , SQLAGENT

what is the rowcount of the table?

Hi kingston

Thanks for the reply

Actually the whole idea of my posting this problem was to not to use dynamic SQL.

Thanks again

Thank you very much for the solution.

Sometimes there is a very easy solution for a complicated problem and we just cant think of it

Thanks again.

Thanks for the feedback.

It was my pleasure to help.

And no need to thank me as i have also got many solutions from this site so just doing my part to...

Thanks for the reply

But sorry i didn't gave a complete idea of the problem

The problem is like this

Declare @tab1 table (ID int, Name varchar(10))

insert into @tab1 values (1, 'One')

insert into...

this will solve the problem. Here i have used rownumber to find the lowest priority and rowid.

Declare @priorityupdate table

( aid int,

priority int,

rowid int,

Transaction_V nvarchar(1))

Insert into @priorityupdate(aid,priority,rowid) values(1234,2,2)

Insert into @priorityupdate(aid,priority,rowid)...

Please post some DDL data

please provide table structure and test data

Assuming ID column of hdr table is identity , as u havent given any details about it.

Declare @dtl table (id Int, fk Int, dtl_note varchar(100))

insert into @dtl

values (1,1,'first')

insert...

thanks GilaMonster

those are really good articles.

XML can help u in this.

Get those external files in XML if possible. As in xml u can tag the values. this will definitely solve your problem.

You are using different sets of table in each query. there is no scope in further optimization.

Only chance remain is that if you can check the indexes and optimize it.