Hi, i have two tables AND would LIKE TO get data BY combining both.
here IS my data
WITH hist AS
(
SELECT To_Date('4/23/2010','mm/dd/yyyy') dt, 999 alias, 'PROC' dom FROM dual UNION ALL
SELECT To_Date('4/27/2010','mm/dd/yyyy') dt, 999 alias, 'LON' dom FROM dual UNION all
SELECT To_Date('4/1/2010','mm/dd/yyyy') dt, 111 alias, 'SOC' dom FROM dual UNION all
SELECT To_Date('4/10/2010','mm/dd/yyyy') dt, 111 alias, 'NAO' dom FROM dual UNION ALL
SELECT To_Date('3/23/2010','mm/dd/yyyy') dt, 222 alias, 'PSE' dom FROM dual
),
final AS
(
SELECT To_Date('2/26/2010','mm/dd/yyyy') dt, 999 alias FROM dual UNION ALL
SELECT To_Date('4/22/2010','mm/dd/yyyy') dt, 999 alias FROM dual UNION all
SELECT To_Date('4/26/2010','mm/dd/yyyy') dt, 999 alias FROM dual UNION ALL
SELECT To_Date('4/30/2010','mm/dd/yyyy') dt, 999 alias FROM dual UNION ALL
SELECT To_Date('2/25/2010','mm/dd/yyyy') dt, 111 alias FROM dual UNION ALL
SELECT To_Date('2/26/2010','mm/dd/yyyy') dt, 222 alias FROM dual UNION ALL
SELECT To_Date('4/22/2010','mm/dd/yyyy') dt, 222 alias FROM dual UNION all
SELECT To_Date('4/26/2010','mm/dd/yyyy') dt, 222 alias FROM dual
)
the output should be as follow(without the extra blank line of course)
DT ALIAS DOM
2/26/2010 999 PROC
4/22/2010 999 PROC
4/26/2010 999 LON
4/30/2010 999 LON
4/27/2010 999 LON
4/23/2010 999 PROC
2/25/2010 111 SOC
4/1/2010 222 SOC
4/10/2010 222 NAO
2/26/2010 222 PSE
4/22/2010 222 PSE
4/26/2010 222 PSE
so what i am doing here is as follow, take one row in hist table (4/23) and join with final table and give me all rows in final table
where dt <= to the row in hist table and include the row from hist table.
this logic will give me rows 2/26/2010,4/22/10 4/23/2010
then the second row in hist table (4/27/2010) wiill get all rows
in final table that is <= to the current row and pick up the rows starting from the row > than the last row where the 4/23/2010 finished off
in this case the output will be 4/26/10, 4/27/2010(we need to include row from hist)
since there is no row in hist that is greater than 4/30/2010, this date will still be display and dom column value should be taking from the max date in hist
which is 4/27/2010. see output above
this sound a little confusing to explain but look at output of what to expect as output. the other ids should follow the same logic
can someone help write a query for this? thanks