querying dates on 2 tables

[jelee2]

Hi,
I have 2 tables, one called timeslot, the other timeslotChanged.
timeslot has the date fields:
1)date(current timeslot date)
2)originalCreationDate (same as date if date hasn't been changed)
3)lastChangedDate (same as date if date hasn't been changed)

timeslotChanged has the date fields:
1)date(current timeslot date)
2)lastChangedDate
3)creationDate (original creation date)

What I want to do is query the database to select the previous timeslots from a certain date but NOT including changes made after that date. Therefore it should produce the timeslots that were either not changed, or the timeslots as they were on that date (they may have been changed afterwards, but I do not want them).

I hope you understand this dilemma.

 

[rac2]

So there is a target date that determines which rows you want to see. Lets refer to it as AsOfDate, and assume it has a value provided whenever you run the query.

You want to know what the "timeslots" looked like on the AsOfDate.

A timeslot has a creation date which is stored in two places:
timeslot.originalCreationDate and timeslotChanged.creationDate

A timeslot has a date which is stored in two places:
timeslot.date and timeslotChanged.date

A timeslot has a date of change which is stored in two places:
timeslot.lastChangedDate and timeslotChanged.astChangedDate


One wonders why you have two tables.


Working with one table, timeslot

SELECT *
FROM timeslot
WHERE
       originalCreationDate = lastChangedDate
  AND originalCreationDate < AsOfDate

That will give you the timeslots that existed and were unchanged as of the target date.

SELECT *
FROM timeslot
WHERE
       lastChangedDate = (
                           SELECT MAX(lastChangedDate)
                           FROM timeslot
                           WHERE  
                                 lastChangedDate < AsOfDate
                          )

That might give you the timeslot that was changed prior to the target date.

But really I don't feel like I really understand your data, so I don't know whether this will help or not.

Oh yes, the final piece, combine the results of these two queries with a UNION.