Here's a realistic example.
Source table:
http://pastebin.com/uNJxreER
Basically, think of it as forward looking temperature forecasts. Every hour a new forecast is published for the next 24 hours. So I have two dimensions: 1) the hour for which the forecast value is effective and 2) the hour in which that forecast value was published. I want to be able to do things like select only the 3 hour ahead forecast for each hour, or the forecast for the next hour after. So I have the following structure:
where:
pts = published time stamp
ptshour = calculated column that represents the # of hours after a fixed point in time that this data was published
tshour = calculated column that represents the # of hours after the same fixed point in time that this data is effective for
ts = effective time stamp
the tshour and ptshour columns can be used to get offset, so for example, "WHERE ptshour+3 = tshour" will return only those values that were published 3 hours prior to the effective hour.
Query and correct result:
http://pastebin.com/D8HJJg8Y
Differences between this table and the actual table I'm working with:
1) I have another column called "LocID" that represents one of many different locations
2) I have another column called "type" that represents up to 3 different forecast types
3) a formulaic unique identifier for each row
4) the sample table only has a few values for each hour. the actual data would have many times that (up to 32+ versions per hour)
so... if you multiply all that out, the table is currently just under 28 million rows. Except for this one query that I can't seem to figure out, it seems to be very efficient, and I can select just about anything I'm looking for in miliseconds. It's only this left join that's stumping me