SELECT RC.RETAILER_CD, RC.INV_NO, RC.DOC_DT, SUM(RC.SLS_ACT)
FROM RANGE_CREATION RC, ACTIVITY_TXN AV
WHERE RC.DOC_DT > ACTIVITY_START_DT  AND RC.DOC_DT <= ACTIVITY_CLOSURE_DT + 14
AND RC.RETAILER_CD = AV.CHANNEL_NAME
AND AV.TYPE = 'Range Expansion'
AND  EXISTS(
SELECT RC1.RETAILER_CD, RC1.INV_NO, RC1.DOC_DT
FROM RANGE_CREATION RC1, ACTIVITY_TXN AV1
WHERE RC1.RETAILER_CD = RC.RETAILER_CD
AND RC1.RETAILER_CD = AV1.CHANNEL_NAME
AND RC1.DOC_DT> AV1.ACTIVITY_START_DT - 90 AND DOC_DT < ACTIVITY_START_DT
--AND RC1.INV_NO <> RC.INV_NO
AND RC1.ITM_CD <> RC.ITM_CD
AND AV1.TYPE = 'Range Expansion')
GROUP BY RC.RETAILER_CD, RC.INV_NO, RC.DOC_DT
HAVING SUM(RC.SLS_ACT)>50
ORDER BY  RC.RETAILER_CD, RC.INV_NO, RC.DOC_DT;