You can use a splitter function, and a cross tab to get what you are looking for

DECLARE @TestData table (JoinedData varchar(50));

INSERT INTO @TestData ( JoinedData )
VALUES (...

In my previous post I stated that you would find more data anomalies - Wonder how I knew that.

This is not something that can be fixed with a single query. ...

Based on the latest sample data that you provided, this code works.

However, I fully expect that you will get other non-compliant records.

You can keep tweaking the code to the point...

Your downfall here is going to be trying to do it all at once.

You cannot touch the vendor DB, but there is nothing stopping you from creating your own staging...

That speaks to a record whose data does not match the pattern of data that was used for creating the solution at hand.

You will need to find the record(s) that...

That code will not work for a declared table variable.

It is for a table parameter as requested in the original post.

Also note that the code is looking for the declared...

Oops.  I think I hit REPORT instead of QUOTE.

Using NOLOCK everywhere for performance means that your reports are most likely incorrect.  How can business be expected to make informed decisions...

SELECT TableVariableTypeName = tt.name
     , ColumnName = c.name
     , ColumnNumber = c.column_id
    , [DataType]  ...

So, why not copy the original data to your own table in your own DB, then manipulate it from there?

If you need to cast the value, then simply do it

Thank You Des,

That works well. Could i now use this hidden table with the results to then populate another table using SSIS? Would there be any issues with it...

Des,

That is spot on, worked wonderfully, so thanks.

One last thing, can i do a WHERE clause on the "BookingDate" field to only show bookings for the last two hours?

I...

Using basic math substitution .....

-- Fee = Sell * FeeMargin

-- Nett = Sell - Cost - Fee

-- NettMargin = Nett / Cost

----------------------------------------------------------------------------------

-- NettMargin = ( Sell - Cost - Fee...

How to post data/code on a forum to get the best help.