A Tale of Two Tables

[ALewis06]

I have one table TestDataFcstTime_97.xls where time was forecasted for some projects we're working on. The second table TestDataActualTime_97.xls is the time that was actually recorded for projects. In the forecasted table, the weeks are the column headings; in the Actual table the weeks are recorded as rows under the Time Date column. How can I bring the two together so that I can take a look at variances?

I have attached sample data for both tables.

 

[plog]

How can I bring the two together so that I can take a look at variances?

By fixing your tables. Specifically TestDataFcstTime_97.xls. Specific values should never be names (table nor field). Time to pay the penance and structure that table correctly and move your data to it--with the dates as values in a field as opposed to field names.

 

[ALewis06]

I understand that. I didn't design these tables; I inherited this data. One comes from SQL Server Reporting Services and the other from an in-house application. Each time I do the data pulls, the data comes to me that way. I have to merge these two on a regular basis so I was hoping for help on how to best and most efficiently do so, to make it easier going forward.

thx

 

[plog]

When you receive a file not in a good format, you should build a process to move the data to a good format. That way, you can build the necessary queries on the good structure and whenever the data is refreshed your queries simply work to produce the correct data.

In general this is what I do for these situations:

Create 1 warehouse database to house the properly structured tables. This will be a permanent database in that you only need 1 file.

Create a staging template database where you will clean up the bad data. This database will be copied each time you import new data.

In the staging database you will import the external data to temporary tables and have the good tables from the warehouse linked in.

In the staging database you will create queries to move the data from the temporary tables to the good tables. This might take a few queries that even make even more temporary tables.

Looking at your bad table, my guess this will involve 1 query for every date named field you have.

 

[ALewis06]

Great direction, thanks for your guidance on this process. It's as you said: the hard part is the beginning but if done right, it will be much less painful moving forward.