Sub Query In Join Assistance

1

Hi All,
I am a self taught SQL novice, and have got completely stuck trying to accomplish something.

I have a query with a date parameter and 2 tables I need to link together, I need to link from Table a to Table b, however the link will return a list of values as the records are a series of dates, with a value at that date.

I need to return the value field of the record where the date is the most recent date that is less than or equal to the date parameter. I think I need to use a sub query in the join to pull all records where the date is less than or equal to the parameter date, then link to the MAX of that sub query, but for the life of me I can't get it to work. Any assistance would be great, here is my code thus far;

LEFT OUTER JOIN [PMSCoreValueEntityLinks] AS [manMaintWeight] on [InstructionSetGroups].[InstructionSetGroupID] = [manMaintWeight].[EntityPrimaryKeyId]
AND [manMaintWeight].[TableCatalogId] = 1015
LEFT OUTER JOIN [PMSCoreValues] AS [mmwCoreValues] on [manMaintWeight].[PMSCoreValueID] = [mmwCoreValues].[PMSCoreValueID]
LEFT OUTER JOIN [PMSCoreValueGroup] AS [mmwCoreValueGroup] on [mmwCoreValues].[PMSCoreValueGroupID] = [mmwCoreValueGroup].[PMSCoreValueGroupID]
AND [mmwCoreValueGroup].[PMSCoreValueGroupID] = 42
LEFT OUTER JOIN [PMSCoreValueDates] AS [mmwCoreValueDates] on [mmwCoreValues].[PMSCoreValueId] = [mmwCoreValueDates].[PMSCoreValueID]

I need the field [PMSCoreValueDates].[DateFrom] to be the field which is the max of all less than or equal to {?reportDate}, so adding something like this at the end of the join;

AND [PMSCoreValueDates].[DateFrom] MAX(All dates <= {?reportDate}

Thanks in advance.

2

There are several ways to approach this...

OUTER/CROSS APPLY:

OUTER APPLY (Select Top 1 x.DateFrom From PMSCoreValueDates x WHERE x.PMSCoreValueID = mmwCoreValues.PMSCoreValueID AND x.DateFrom <= {reportDate parameter} ORDER BY x.DateFrom desc) AS mmwCoreValueDates

DERIVED TABLE:

LEFT JOIN (Select x.PMSCoreValueID, max(x.DateFrom) AS DateFrom From PMSCoreValueDates x WHERE x.DateFrom <= {reportDate parameter} GROUP BY x.PMSCoreValueID) AS mmwCoreValueDates ON mmwCoreValueDates.PMSCoreValueID = mmwCoreValue.PMSCoreValueID

SUBQUERY - return just the date from column:

DateFrom = (Select max(x.DateFrom) From PMSCoreValueDates x Where x.PMSCoreValueID = mmwCoreValue.PMSCoreValueID And x.DateFrom <= {reportDate paramter})

The first option allows you to get all related columns from the PMSCoreValueDates table - the others allow for only that one value. The derived table could include additional columns but you need to add each column to be returned in some type of aggregate which may not return the actual value from that row.