All Forums Database
MC_TDUser 2 posts Joined 01/12
22 Jan 2015
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

Thanks Maria
Raja_KT 1246 posts Joined 07/09
22 Jan 2015

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.

dnoeth 4628 posts Joined 11/04
23 Jan 2015

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

You must sign in to leave a comment.