All of the suggestions so far with converts and udfs are fine, but I would approach it by building a conversion table

CREATE TABLE dbo.DateConvert(

DB2Date AS VARCHAR(10),

CalendarDate AS DATE

)

INSERT INTO DateConvert(DB2Date,CalendarDate)...

Joe's back!

Seriously folks, The table structure may not be the best but you rounded on the guy pretty hard.

@joe specifically, I know we have had our run-ins in the past...

If the code value is embedded in the text you could use a derived column task with a REPLACE() function.

If You want Test1 = A, Test2 = B, Test3 =...

If I understand the OPs requirement then the proposed solutions are correct by overly complicated.

The OP wants to always take the first three columns and doesn't care about the rest.

When...

use Konesan's file Watcher task. google for it; its on the SQLIS site somewhere

Add a task to the top of the process that sets a variable to be the system time, then on the success arrows from first Execute SQL task, set the...

For me, an Episode is a DIMension (probably Type II SCD) and EpisodeEvents and EpisodeStatus are FACTs.

If you are going to drop and rebuild you dimensions, you need to be really careful that the keys are always alocated in the same order or you could end...

That is a huge IT DEPENDS!

If you can identify a reliable delta of changed records from the source then I would look at an incremental patters (don't forget about deleted...

free text should not be stored in the DW, it is not a member, measure or key 🙂

If they are using some 'feature' of the free text then that should...

I would start with Agile Data Warehouse Design by Lawrence Corr and Jim Stagnitto.

The focus of the book is very much on workingwith the end users and understanding what they...

It looks like a simple message broking pattern.

For me the first thing I would look to do is refactor the messages to XML; this will increase the size of the...

well put G.

Just to add to the discussion. If you wanted to be able to SUM() across the date dimension you would need to record stock MOVEMENT not stock...

Go with your original design;

StoreKey and InvoiceNumber are your member identifier for aggregation.

There is no need based on your stated problem to have an invoice dimension. If you have...

the short answer is you can't , you havre to convert it to a full chart and turn off all the axis, chart title and legend