What's with all the declared but unused variables?

You've got an error handling block, but no error checking anywhere.

Doesn't matter how you capture the data. You've got 15 fields all storing the same thing in one table. That's bad db design. It may make the capturing easier, but...

A subselect in the SELECt clause can only return 1 field. Join it is as a subquery.

Do you want the latest reply for a post? (assuming so for the below...

Can you fix the database design? At the moment it's violating first normal form (table contains repeating groups). What happens if it's decided that a month of data needs to...

I just glanced over it quickly, but I've got a feeling that it can be optimised a bit.

Can you please post table definitions, sample data and expected output. Also the...

No problem. What part don't you understand?

The case statement takes the day of the week (Sunday=1, Monday=2,....) and based on that decides how many days to add to get 7...

Not particuarly elegent, but it works.

set datefirst 7

DECLARE @DayOfWeek TINYINT

SET @DayOfWeek=datepart(dw,getdate())

select DATEADD(dd,CASE WHEN @DayOfWeek<5 THEN 9 WHEN @DayOfWeek=7 THEN 10 ELSE 11 END, GETDATE())

For today (Thurs 20th Oct) this returns...

No. Stored procedures may have output parameters but functions may not. See the following from BoL.

A user-defined function takes zero or more input parameters and returns either a scalar value...

A job that runs once a day would work.

Job checks for all records where datediff(dd,TraceDate,GETDATE())=7 AND TRStatus='Closed' The =7 ensures that the same record won't get flagged two days...

No problem.

I can't see anything wrong either. If you replace the sp_executeSQL with Print, what's the resulting statement? (checking to see that nothing strange has snuck into the variables)

That would be true, if there wasn't a TOP 1 in each of the subqueries.

The final order of the union is undefined, but it consists of the first records in...

This however does work

select Col1, Col2 FROM

 (select top 1 Col1, Col2 from TableA order by Col2) a

UNION

select Col3, Col4 FROM

 (select top 1 Col3, Col4 from TableB order by Col4) b

How many processors does your server have?

What connection settings/SET options is the app using?