asked
SubQuery Last Historical Date
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...?
your sub select looks like it already is getting the max date? `MAX(hp.prc_eff_dt)` ?
I can't get it to function correctly - these wonderful error messages are giving awesome hints... 1 Incorrect syntax near the keyword 'WHERE'. 2 Incorrect syntax near ')'.
we can't comment on syntax issues if you don't post the whole code snippet, for example what you've got above is immediately wrong because of the `select ....` - which I understand is just a placeholder, but doesn't help if the error is syntax.
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
that is wrong because you are trying to reference an aggregate value, and you have no aggregate
Take a look at [this article][1] 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. [1]: http://
@Oleg late or not, that's a very complete answer. + 1 to you
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
