Have you tried using MERGE for the upsert, instead of your current two-stage process?

Shredding XML isn't instant - you may gain by separating this from the table changes too:

SELECT

TradeID...

Stephanie Giovannini (1/3/2012)

---

...I also wish there was a way to declare a table variable and have its column structure exactly match another table's structure without writing out all the columns...

I've always used a local temp table to capture OUTPUT.

Scanning BOL, you can't OUTPUT to a remote view - but what about a 'local' one? Use $action and filter...

As...

You're welcome.

Did you resolve the problem? Would you like to share with us the resolution?

Look up GROUP BY in books on line (BOL).

You can use CASE in a WHERE clause.

mic.con87 (1/3/2012)

---

@ 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...

Where A is a member of...

Try this query, using different values of Groupname. If it works, then we'll pivot the results using FOR XML PATH to put them into a comma-delimited list:

;WITH rCTE AS (

SELECT...

Run the query outside the stored procedure - always much easier to debug - and chop out some unnecessary bits.

DECLARE

@LNK_File_ID numeric(18, 0),

@LNK_Client_ID numeric(18, 0),

@ExpanseDate datetime

SET @ExpanseDate = '29/12/2011 12:00:00...

Thanks for the feedback - I won't hold my breath while you're still testing 😉

DENSE_RANK() and ROW_NUMBER() are used together like this to resolve subsets of the same partition, where...

What datatype is E.[ExpanseDate]?

,CONVERT(Datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]

E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID

...

mic.con87 (1/3/2012)

---

...@ChrisM@home: Your Query is very close to my desired solution. The only issue is that it selects the max DueDate for a particular month regardless of whether this is...