Unfortunately, your expected output does not match your sample data (e.g. there is no issuerCik with a value of 1000047).

Also, the table structure of your expected output is not clear...

The most helpful link I found on the web was: http://devio.wordpress.com/2008/03/04/retrieving-xml-data-in-sql-server-2005/ (searching for "sql 2005 Msg 9402").

Interesting part when playing around with the data on my machine: I could easily...

You need to add the namespace definition before your query:

;WITH XMLNAMESPACES (

yournamespace 'your namespacefiles'

)

SELECT

If you have problems getting it to work, please post the few...

We do it pretty much the same with just a little difference:

Instead of having three tables like Lowells solution has, we're using one table with positive values for received /...

It looks like either your result set does not match your requirement or your requirement is still unclear...

Example:

It shows that there is more than one row for...

This common requirement usually can be resolved using a calendar table.

Search this site for "calendar table" and you'll find a variety of solutions and discussions related to it.

If you have...

If I understand correctly you want to have the rollup result value before the values rolled up as opposed to the SQL standard.

The closest I could come up with is...

One possible reason for not having an answer yet might be your rather large xml together with a missing result set to compare to.

Try to reduce your sample xml to...

Simple answer: Yes and no. 😉

Yes, if you would wrap your query into a subquery or...

I thought about providing some sample code for a set based solution, too. But since I couldn't find any place in the code where the OP used the variable @entrydateconv...

A few things to notice:

1) (regarding your question)

The problem is with @entrydateconv = DATEADD(day,-29,getdate()) .

Since you "normalized" @entrydateconv, your WHERE condition will only be true exactly at midnight for each...

Hi Nabha,

I'll stop guessing and step back until I see some expected results (now that you've done the first part of providing sample data 😉 ).

It sounds like a job for Service Broker.

It's a little bit tough to get it started, but once it's runnning, it's running...

If the following won't give you the expected result you need to follow Nabha's advice and give us something to test against.

For details on how to post sample data please...

You could have a look at the following articles referenced in my signature: CrossTab or Dynamic Cross Tab (if DataSrc contains an unknown list of elements).

I assume you're using SQL2000...