hi all, i am having problem with getting dates that is greater than others. i am using oracle 9i
here is my data sample
WITH table1 AS
(
SELECT To_Date('3/13/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT To_Date('3/17/2010','mm/dd/yyyy') date1, 'smith' name FROM dual
),
table2 AS
(
SELECT To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual
)
these two tables need to be join in such a way that this logic should apply
for example, i want to take one row in table1 in this case it will be 3/13/2010 and go to table2 and get all the
rows that are less or equal to 3/13/2010.
so FOR 3/13/2010 the output should be
DATE1 NAME
1/16/2010 john --since this dates are less than 3/13/2010, take the name value from 3/13/2010
3/12/2010 john --same as above
then oracle will move on to 3/17/2010 row and again it should go to table2 and take all the dates that are LESS OR equal than 3/17/2010 but greater than 3/12/2010
i said greater THAN 3/12/2010 because 1/16 AND 3/12 were pick up BY 3/13/2010 previously
in this case the output for 3/17/2010 will be
DATE1 NAME
3/16/2010 smith
if you notice there is not more date in table1 and table2 has one more date which is 3/20/2010 in this case, this row should be display
as well and output should be
DATE1 NAME
3/20/2010 smith -- take name from max date in table1 that is <= to this row( 3/20/2010)
i try using some analytic function like lastvalue but since i am using oracle 9i the ignore null is not supported.
so i was trying to get the max date and do >= or <= but i wasnt successfuly
the final output should be.
DATE1 NAME
1/16/2010 john
3/12/2010 john
3/16/2010 smith
3/20/2010 smith
also i could have the following data IN table2
SELECT To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/25/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual
IN this dataset output should be
DATE1 NAME
1/16/2010 john
3/12/2010 john
3/16/2010 smith
3/20/2010 smith --take smith name from max date in table1 that is <= to this row( 3/20/2010)
3/25/2010 smith --take smith name from max date in table1 that is <= to this row
4/20/2010 smith --take smith name from max date in table1 that is <= to this row
another dataset could be AS follow
also i could have the following data IN table2
SELECT To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual
IN this dataset output should be
DATE1 NAME
1/16/2010 john
3/12/2010 john
3/16/2010 smith
if you notice there is no date in table2 that is greater or equal to a date in table1.
in this case the output should be like above.
also table 1 might contain 1 row such as 3/13/2010 and table2 can contain two dates such as 1/16/2010 and 3/12/2010
then in that case the output should be
DATE1 NAME
1/16/2010 john --since this dates are less than 3/13/2010, take the name value from 3/13/2010
3/12/2010 john --same as above
these are different scenario that can happen with the data.
can somebody help write a query with the outputs listed above for the different dataset
remember i am using 9i and some feature of oracle especially analytic function ignore null is not supported.
thanks a lot everyone IN advance