I am trying to choose the last historical date in a subquery.... I need to the last price within the timefame compared to another table... Select..... FROM #TODAY T1 INNER JOIN #HIST T2 ON T1.EM_ITEM_NUM = T2.EM_ITEM_NUM LEFT JOIN PRICE.dbo.T_PRC p ON P.ITM = T1.ITM AND P.CURR_CD = 'H' AND P.PRC_TYP = 'G' AND P.PRC_EFF_DT = (Select MAX(hp.prc_eff_dt) from PRICE.dbo.T_PRC hp where hp.eff_dt BETWEEN T1.EFF_DT AND T1.END_DT and hp.ITM= T1.ITM AND hP.CURR_CD = 'H' AND hP.PRC_TYP = 'G' ) how do i get the max date in the subselect...?
Another way to think about it is what syntatically will not work FROM #TODAY T1 LEFT JOIN PRICE.dbo.T_PRC p ON P.ITEM = T1.ITEM AND P.CURR_CD = 'H' AND P.TYP_ID = 8 AND MAX(P.EFF_DT) BETWEEN T1.PRC_EFF_DT AND T1.PRC_END_DT
Take a look at [this article] I wrote on versioned data. It's not exactly the same as what you're doing, but it's pretty close. I'd suggest using TOP 1 with an ORDER BY, that generally worked better. : http://
To use the subquery like this is not always safe. For example, suppose you have more than one prc\_eff\_dt record for the same date which alsow happens to be the maximum satisfying your predicate. Then you get duplicate records in your results and this might not be what you want. If you need only one match and in case there is more than one then you are OK to just pick whatever, then you can try to replace the left join and the subquery part with the outer apply, like this: select T1.*, p.* -- or whatever you actually need from #TODAY T1 inner join #HIST T2 on T1.EM_ITEM_NUM = T2.EM_ITEM_NUM outer apply ( select top 1 * from PRICE.dbo.T_PRC where eff_dt between T1.EFF_DT and T1.END_DT and ITM = T1.ITM and CURR_CD = 'H' and PRC_TYP = 'G' order by prc_eff_dt desc ) p; This will work if you have SQL Server 2005 or better with compat level of 90 or better. Another possibility would be to leave the left join alone, remove the subquery but add the partitioned by ITM and ordered by prc\_eff\_dt desc row\_number(), but it looks like the outer apply should be cheaper in this case. Oleg