I am having problems using dates from volatile table in a query that requires date ranges. This is my query,
SELECT STT.Location_id AS Location_id ,STT.sales_NO AS sales_NO ,STT.sales_DT ,STT.Location_id AS Location_id ,STT.sales_AMT AS sales_AMT FROM sales AS STT ,TMPTBL_START_END_DATES AS TSED ,TMPTBL_Location_GROUPS AS TSG WHERE STT.Tender_Type_CD = TT.Tender_Type_CD AND TSG.Location_id = STT.Location_id AND TSG.GROUP_ID = TSED.GROUP_ID AND TSG.Location_id IN(51) AND ((STT.sales_DT BETWEEN TSED.startDate1 AND TSED.endDate1) OR (STT.sales_DT BETWEEN TSED.startDate2 AND TSED.endDate2)) ORDER BY 3;
my error happens in line
AND ((STT.sales_DT BETWEEN TSED.startDate1 AND TSED.endDate1) OR (STT.sales_DT BETWEEN TSED.startDate2 AND TSED.endDate2))
If I replace TSED.startDate1 with a typed date this query works fine, but I am trying to reference dates entered in a volatile table created right before this query begins.
Any help will be greatly appreciated it.