I finally managed to resolve the required format issue.

I built two tables one for the headers row VCR\CCR and one for lines BTP\CTD. If you are interested they were...

Apologies for the bad format, attached is the DDL & DML Statements.

Insert statements;

INSERT INTO [dbo].[VehicleAnalysisVCR]

([VCRRecType]

,[VCRCustNo]

...

I have made a header table called [VehicleAnalysis_Header] which holds the VCR\CCR records with unique primary key.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[VehicleAnalysis_Header](

[Head_ID] [int] IDENTITY(1,1) NOT NULL,

[VCRRecType] [varchar](3) NULL,

[VCRCustNo]...

DDL is below and some sample data is attached, the required format is pipe delimited | | | | etc...

CREATE TABLE [dbo].[VehicleAnalysisVCR](

[VCRRecType] [varchar](3) NULL,

[VCRCustNo] [int] NULL,

[VCRCostCentre] [varchar](max) NULL,

[VCRCollationFlag] [varchar](2) NULL,

[VCRPrintFlag]...

I found and fixed the problem in the CTE's I added more joins and this removed the No Join Predicate, thanks for the replies.

something like this;

Select

SUM (CASE WHEN DATEPART(m, [FuelTranDate]) = DATEPART(m,GETDATE()) THEN LastOdo ELSE '' END)

- SUM(CASE WHEN DATEPART(m, [FuelTranDate]) = DATEPART(m, DATEADD(m, -1, GETDATE())) THEN Lastodo ELSE ''...

Thanks a lot for the replies. Not sure why it didn't work the first time when I had @SaleID as the output.

Thanks for the replies.

None of these work and the @@rowcount is also only returning '1'

,SUM([>7 Days]) OVER(PARTITION BY f.Country ,f.CompanyName ,f.InvoiceID ,f.SupplierCode ,f.InvoiceNumber

,f.[Scan Date] ,f.[Approval Date]...

I added a primary key to the AUD currency table and tried this. it returns the same as the cursor, multiple emtpy sets, can anyone detail what is wrong.

DECLARE...

Yeah I just created a rate table, dbo.aus for the first set. trying to make it more dynamic so the rates can be updated and automatically and if there's match...

Thanks Orlando and Alan for the replies.

Is dbo.splitcsv a reserved table or function somewhere as i am unable to find it within the DB?

I resolved it using the code below, you cant use the derived column replace function and datepart, so i had to make it as a new column and in a...

Orlando Colamatteo (1/27/2016)

---

It is still not clear to me what you are trying to do.

Or are you looking to copy data from an XML column in one server...

The end result i am trying to achieve is to export the XML into another table or tables depending on the data structure, then write some reports via SSRS.

select Col1...