I am not clear when it is "nearest-greater-date-lookup-through-sql". A quick look at the expected output looks like, row ordering and case when statement can be conditioned according as required.

something like this:

select a.e1,a.sd,b.rd from (select empid e1,submt_dt sd, row_number() over(order by empid ,submt_dt ) rn1 from emp) a,

(select empid e2,resp_dt rd, row_number() over(order by empid ,resp_dt ) rn2 from emp_resp)b where a.e1=b.e2 and a.rn1=b.rn2 order by a.sd

Or maybe I miss something?

Raja K Thaw

My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1

Street Children suffer not by their fault. We can help them if we want.

Hi Maria,

there are multiple ways to get your result.

If the number of rows per value is low and *emp_id* is the PI in both tables this old-style solution using a non-equi join plus aggregation might be ok:

SELECT e.emp_id, e.subm_dt, MIN(er.resp_dt) FROM emp AS e JOIN emp_resp AS er ON er.emp_id = e.emp_id AND er.resp_dt > e.subm_dt GROUP BY 1,2

You might also try an OLAP solution merging both tables in one:

SELECT emp_id, subm_dt, MIN(CASE WHEN x = 2 THEN subm_dt end) -- find the next resp_dt OVER (PARTITION BY emp_id ORDER BY subm_dt, x DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM ( SELECT emp_id, subm_dt, 1 AS x -- table indicator FROM emp UNION ALL SELECT emp_id, resp_dt, 2 AS x FROM emp_resp ) AS dt QUALIFY x = 1 -- return only rows from emp table

Dieter

Hello

I have two tables EMP and EMP_RESP,

EMP holds EMPID and the date a request was submitted by the employee

EMP_RESP holds the EMPID and the date erquest was responded

Expected OutPut

My Output using the SQL provided below is,

The SQL I used,

Request guru's to provide inputs on where I am going wrong, any advice/correction or help is much appreciated.

Thanks in advance