Finding the nearest greater date lookup through SQL

Database

Finding the nearest greater date lookup through SQL

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

EMPID SUBM_DT
100 4/1/2012
100 2/1/2013
100 2/8/2013
100 5/1/2014
EMPID RESP_DT
100 4/30/2012
100 2/16/2013
100 2/26/2013
100 8/31/2014

Expected OutPut

EMPID SUBM_DT          RESP_DT
100 4/1/2012 4/30/2012
100 2/1/2013 2/16/2013
100 2/8/2013 2/26/2013
100 5/1/2014 8/31/2014

My Output using the SQL provided below is,

EMPID SUBM_DT         RESP_DT
100 4/1/2012 4/30/2012
100 2/1/2013 2/16/2013
100 2/8/2013 2/16/2013
100 5/1/2014 8/31/2014

The SQL I used,

SELECT 
y.SUBM_DT
, y.EMP_ID
, y.xdate

FROM
(
SELECT
T.SUBM_DT
, t.EMP_ID
, MIN(X.RESP_DT) OVER (PARTITION BY T.SUBM_DT,T.EMP_ID) AS xdate
, MAX(X.RESP_DT) OVER (PARTITION BY T.SUBM_DT,T.EMP_ID) AS Mdate
, MIN(X.RESP_DT) OVER (PARTITION BY T.EMP_ID ORDER BY T.SUBM_DT ROWS 1 PRECEDING ) AS STRT_DT
,CASE
WHEN STRT_DT = DATE '9999-12-31' THEN X.RESP_DT
WHEN STRT_DT + 1 < X.SUBM_DT THEN STRT_DT + 1
WHEN STRT_DT + 1 = X.SUBM_DT THEN X.RESP_DT
ELSE X.RESP_DT
END AS RCVD_DT
FROM
EMP t
INNER JOIN
EMP_RESP x
ON
X.EMP_ID=T.EMP_ID
AND X.RESP_DT>= T.SUBM_DT
WHERE EMP_ID=100
) y
INNER JOIN
EMP_RESP AS z
ON
Z.EMP_ID = Y.EMP_ID AND Z.RESP_DT = y.xdate

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

Thanks in advance

Tags (1)
2 REPLIES
Enthusiast

Re: Finding the nearest greater date lookup through SQL

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?

Junior Contributor

Re: Finding the nearest greater date lookup through SQL

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