In my Analysis screen when the users selects code 1 and code 2, I have
to compare both the values of code 2 with the value of code 1 and display data.
The user is given the option to select two codes, and start and the end date.
So now if the user selects Code 1 and Code 2 and startDt:2005/04/15
End Date: 2005/04/13. It has to display as follows on the screen.
Code Purdate ItmValue
1 2005-04-15 08:00:01 360
1 2005-04-15 08:30:00 200
1 2005-04-13 09:01:00 390
Purdate is the date of Code 1. ItmValue is of
It has to display all the records of code 1 and then for each record of
code 1, compare the Purdate of Code1 to the PurDate of Code2. It has to
pick the closest matching purdate of Code 2 to the purdate of Code 1.
I would appreciate if anybody could help me how to build the
sql for this.
(SELECT itm_value FROM purchase t2
WHERE t2.code = v_code2
AND ABS(t2.PURDATE - t1.purdate) = (SELECT MIN(ABS(t2.purdate - t3.purdate))
FROM purchase t3
WHERE t3.code = v_code1
AND TO_DATE(t3.purdate) BETWEEN TO_DATE(v_date_start)
AND TO_DATE(t2.purdate) BETWEEN TO_DATE(v_date_start)
FROM purchase t1
WHERE TO_DATE(t1.purdate) BETWEEN TO_DATE(v_date_start)
AND t1.code = v_code1