I suspect that this requirement would be way easier to do loading the data into a staging table and then using T-SQL.

Maybe some more veteran SSIS users have a quick...

As for the 'six degrees of kevin bacon' analogy, I thought it was a good fit to the problem you describe 🙂

Kind of a strange requirement ...

Apologies for wading into the "IT union" side discussion, but I have to agree that a union is not really an ideal fit in general for developers and DBAs.

And its...

Yeah, I'm not surprised.

If the deepest # of iterations isn't that high, the while loop performance would not be that bad.

The recursive cte can't tell which matches it has already...

Here is the recursive CTE solution.

Not sure if it will be any quicker at all, and you still need to know the max iteration depth to put...

Thats not what he wants if you look at his expected results. He wants to do a "six degrees of kevin bacon" on his data, finding all the parties...

I think if you have a fair amount of annual vacation, it helps to have some annual traditions for how some of it is used. Some of it may...

Sorry to be captain obvious, but you could always save your nicely formatted view definition as a file, and open it when you need it as opposed to doing a...

You can do a derived column that performs the conversion.

Take a look and tell me if this is what you want ... I included one case like the one I was asking about.

Data types are incorrect as I started...

So given that period table you'd never have a record like:

XYZ l456 DEF 201301

Where there were no earlier routing records for XYZ l456?

What should you return if there is no prior record in routing history for that period (ie: item introduced a month later)?

Also, what are you grouping by? Just item...

Not sure that is a good example, smgilbert101. You want all rows, but you want specific columns.

You may have a use for all the columns that currently exist,...

I suppose it depends how you define 'messy'.

You can probably achieve a better solution with a calendar table, but if you have a lot of these status tables, thats still...

I have worked in places that required the card to be displayed, and ones that did not. And ones that did where it was not enforced. In some...