Hi All,
I need to get nearest date by comparing it with 2 different tables.
TABLE A
=======
ID TRANSACTION_DATE
===== =================
001 9/16/2011
002 9/16/2011
003 9/16/2011
004 9/16/2011
005 9/16/2011
006 9/16/2011
TABLE B
=======
ID TRANSACTION_DETAIL_DATE
===== =======================
001 9/16/2011
001 9/17/2011
001 9/15/2011
002 9/16/2011
002 9/17/2011
003 9/17/2011
003 9/15/2011
006 9/17/2011
004 9/16/2011
005 9/16/2011
Need to check nearest date or nearest past date from TABLE B based on TABLE A and TABLE C.
For example, take a ID and DATE from table A and compare it with table B. If TRANSACTION DATE is within X days (here X date will be 1 of 0), then take closest date or past closest date.
X days willbe derived from below table.
TABLE X_DAYS
==============
TYPE VAL
===== ========
DAYS 0 or 1
For example, if X day is give 0 then result set should be
TARGET TABLE
============
ID DATE
==== =====
001 9/16/2011
002 9/16/2011
003 NULL
004 9/16/2011
005 9/16/2011
006 NULL
If x day is given 1, then result set should be
TARGET TABLE
============
ID DATE
==== ======
001 9/16/2011
002 9/16/2011
003 9/15/2011 (past closest date)
004 9/16/2011
005 9/16/2011
006 9/17/2011 (past closest date)
To achive this, i have tried with below query, but somehow i am not getting answer.
SELECT A.ID, B.TRANSACTION_DETAIL_DATE, MAX(CASE
WHEN A.TRANSACTION_DATE = B.TRANSACTION_DETAIL_DATE THEN 3
WHEN A.TRANSACTION_DATE != B.TRANSACTION_DETAIL_DATE AND A.TRANSACTION_DATE = (B.TRANSACTION_DETAIL_DATE - VALTAB.VAL) THEN 1
WHEN A.TRANSACTION_DATE != B.TRANSACTION_DETAIL_DATE AND A.TRANSACTION_DATE != (B.TRANSACTION_DETAIL_DATE - VALTAB.VAL) AND A.TRANSACTION_DATE = (B.TRANSACTION_DETAIL_DATE + VALTAB.VAL) THEN 2
ELSE NULL END) RES
FROM TABLEA A, TABLEB B, (select val from X_DAYS where where TYPE='DAYS') VALTAB
WHERE A.ID = B.ID
group by A.ID, B.TRANSACTION_DETAIL_DATE
order by A.ID
I need to write SQL query to get this.