Query to fetch the max date
894333Apr 25 2012 — edited Apr 26 2012Hi All,
Taking the below data as sample,we have a requirement wherein we have to fetch the date based on the following criteria.
Item Cost Transaction Date
-------------------------------
A 23 12-feb-2012 1:00 AM
P 23 12-feb-2012 2:00 AM
B 24 13-feb-2012
C 43 14-feb-2012 3:00 AM
M 43 14-feb-2012 8:00 AM
D 12 16-feb-2012
User will pass date as the parameter and if the table has Transaction Date same as the date parameter then that date is picked.
Eg: if the parameter passed is 12-feb-2012, since it is present in table then 12-Feb-2012 2:00AM will be fetched. The table has 2 records, the onewith latest date is picked.
Now if the table does not have Transaction Date same as the date parameter then the max date previous to that date passed as parameter should be picked.
Eg: if the parameter passed is 15-feb-2012, since 15- FEB-2012 is not present in table, it should pick 14-FEB-2012 8:00 AM (latest date).
I am not able to frame queries for the above criteria.
If someone can help me with the same, it would be of great help.
Regards,
Shruti