Thanks for the replies. The plan is to delete duplicate records, there's nearly 100k records hence I don't want to manually enter the values from the error message.

Thanks for the reply, how do you create a TSV file from a XML\XSLT file?

I have altered the XSL code\file and have managed to get columns in 4 outputs, item, Items, invoice, invoices.

<?xml version='1.0' ?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<Export>

<xsl:attribute name="BatchNo">

<xsl:value-of select="Export/@BatchNo"/>

</xsl:attribute>

<Invoices>

<xsl:for-each select="Export/Invoices/Invoice">

<Invoice>

<AgencyName>

<xsl:value-of select="AgencyName"/>

</AgencyName>

<TransDescription>

<xsl:value-of select="TransDescription"/>

</TransDescription>

<CampaignName>

<xsl:value-of...

ok i have created a new XSL file, but the resulting denormalised XML file header doesn't look right (see attached screen shot new.jpg)

New XSL Code

<?xml version='1.0' ?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template...

Thanks for your help, I got it.

select

--TOP 15 t1.[Spend]

[MediaName]

,[Spend]

,[ClientName]

,[BookingYear]

, ROW_NUMBER() OVER(PArtition by [MediaName] ORDER BY [Spend] DESC) As Row

from(

select

lhe.LHECRELongDesc as [MediaName]

,cast(sum(spo.SPODiscountedCost) as money) as...

Sorry about 17 rows in the expected, that was a mistake should have been in current.

When I use top 15 t1.[spend] it only returns 15 rows from 2013. I would...

Finally found the answer.

Launching the install of a Microsoft ACE OLEDB Provider on a machine with an Office install other than the current one (e.g. 32 on 64) will cause...

I guess i need something like a page break after say 10 categories, is such a thing possible?

Thanks a lot chris, i have never heard of cross join before works.

I ended up with this

UPDATE p1

SET

...

Thanks for the replies, I got it working with a union for each statement. I was originally using two unions (for same DB) and then added a another statement (different...

ChrisM@Work (8/16/2013)

Stefan_G (8/16/2013)

This is a good idea - scan the large tables a single time.

To improve performance even more you should include a WHERE condition for GNLFSMID as well:

WHERE...

Ah perfect Koen thanks heaps.

This code returns exactly the right number of rows and the numbers in the 'closing balance' column are right.

But there is only one calculated field 'closing balance' and when...

Many thanks for the reply. I have slightly modified the code for the actual views. But it is still not group the data togther.

;WITH

cteGeneralLedger

(GNLID,GNLCLIID,GNLCOAID,GNLFSMID,GNLBalanceBase)

AS (

SELECT...

When I try execute the SP I get an error message.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >,...