Help with complicated join condition

This should do it assuming that you have SQL Server 2005 or better. The idea is to select the records from traffic.dbo.adImpressions table such that the returned results are sub-numbered on adScheduleNo basis placing the latest record to the top (RowNumber = 1). Because you mentioned that it is possible that some core.dbo.adSchedule records do not have a match in traffic.dbo.adImpressions table, the left join in combination with RecordNumber = 1 in the ON clause should be used.

select 
    adSched.adScheduleNo, adSched.adNo, adSched.zoneNo, 
    adSched.positionNo, adSched.slotGroupNo, 
    TopAdImpressions.maxImpressionDateTime
    from core.dbo.adSchedule adSched
    left join
    (
        select
            adScheduleNo, 
            impressionDateTime as maxImpressionDateTime,
            row_number() over (partition by adScheduleNo 
                order by impressionDateTime desc) RowNumber
            from 
            traffic.dbo.adImpressions
    ) TopAdImpressions 
        on TopAdImpressions.adScheduleNo = adSched.adScheduleNo 
        and TopAdImpressions.RowNumber = 1;

<!-- Begin Edit

Bill,

Based on your comments, I believe that the query should be restated like this to get the data you need:

;with records as
(
    select 
        adSched.adScheduleNo, adSched.adNo, adSched.zoneNo, 
        adSched.positionNo, adSched.slotGroupNo, 
        TopAdImpressions.maxImpressionDateTime,
        row_number() over (partition by ZoneNo, positionNo 
            order by TopAdImpressions.maxImpressionDateTime desc) RowNumber
        from core.dbo.adSchedule adSched
        outer apply
        (
            select
                max(impressionDateTime) as maxImpressionDateTime
                from traffic.dbo.adImpressions
                where adScheduleNo = adSched.adScheduleNo
        ) TopAdImpressions
)
    select
        adScheduleNo, adNo, zoneNo, positionNo, 
        slotGroupNo, maxImpressionDateTime
        from records
        where RowNumber = 1;

Please let me know if this works

End Edit -->

Oleg

Thanks Oleg,

I'm going to have to post my comments in the "answer" field, in order to properly format my comments (and to give me more space). Hopefully answering my own question won't close this to other answers. (I'll find out soon!)

That helps, but doesn't quite solve the issue. First off, the adScheduleNo changes in each of those first three records, so partitioning on that value doesn't help - each row ends up with a row_number of 1 (each adScheduleRow for which there is one or more matching adImpressions row(s)).

Example: If I have an adImpressions row for each of the three adSchedule rows as above (data sample 3), then this row_number join returns:

92  2011-04-07 11:54:49.923  1
93  2011-04-08 23:54:49.923  1
97  2011-04-09 09:30:49.927  1

I can solve this by changing the partition to partition based on the slotGroupNo instead, but that's not stored in the adImpressions table. I might add that column to help. For now I've just modified that part of the query to include slotGroupNo via a join:

select
  adImp.adScheduleNo, 
  impressionDateTime as maxImpressionDateTime,
  adSched2.slotGroupNo,
  row_number() over (partition by slotGroupNo
  order by impressionDateTime desc) RowNumber
  from 
  traffic.dbo.adImpressions adImp 
  inner join core.dbo.adSchedule adSched2 
  on adSched2.adScheduleNo = adImp.adScheduleNo

This results in the following data for the impressionDateTime:

97  2011-04-09 09:30:49.927  50   1
93  2011-04-08 23:54:49.923  50   2
92  2011-04-07 11:54:49.923  50   3

This seems better, but it still doesn't eliminate any of the extra rows from my final result set (due to the left join). What I end up with is the following (based on the data in sample data 3 above):

The impressionDateTime value is only showing on record 3 (adScheduleNo 97), which is correct, based on the join we did. However, the first two rows are still there, with null impressionDateTime values. Again, due to the left join.
If I use an inner join, it works very nicely, but then I lose all records for which there are no adImpressions records.

I like the use of row_number() with the partition statement. I hadn't used that before, and can see some useful possibilities there for other queries. I love learning something new. :)

Thanks again Oleg!

Bill