[thelist] SQL Question - 'Contingent Columns'

[Luther, Ron]

Hi Gang,


I'm working on a report that has an "interesting" data structure.  Thought I'd touch base to see if I'm on the right track - or if there is an easier approach I'm not seeing.

Let's say I have a table of product shipment information:

Field_1 - Part Number
Field_2 - Month-to-Date shipments through yesterday
Field_3 - MTD shipments through (the day before yesterday)
Field_4 - MTD shipments through (the day before that)

Doesn't look too tough to report the last 3 days of shipments, right?

The "challenge" is in the way the file is updated.

The file is updated 3 times a day by geographical region.  Right now (8am Toronto time) my 'shipments thru yesterday' field has April 15th data for Asia and Europe, but April 14th data for the Americas.  Sometime around 3pm today (Toronto time), that same field will contain April 16th data for Asia and April 15th data for Europe and the Americas.


Now there is a second table that tells me the update status of my data.  It has a structure something like:

Region - Region
Time Period - Calendar date
Column - Data column

So the table would have 9 records, one for each region for each of the last three days telling me what column the data is in:

Asia - 20020416 - Field_2
Asia - 20020415 - Field_3
Asia - 20020414 - Field_4
Amer - 20020415 - Field_2
etc.


I'm thinking of running three small queries to pull off the locations of the "max dates" for each region, [Actually - I think I can do that in one query and a little JavaScript in Brio.], and then structuring a union between three datasets - pulling each region separately.

Is there an easier way?  Any syntax that will let me do a 'Select yesterday from col2 if Asia, but col3 if Europe'?


Thanks,


RonL.

(I finally had this *&%&^ report wired ... tested it in the morning [when the columns where 'aligned'] and it looked great.  Tested again in the late afternoon and it looked screwy - which is when my DBAs finally told me about their wonderful update methodology.  I'm sure there's a tip for Intranet folks in there somewhere!)