Please Need Help as soon as possible.

Tools
Enthusiast

Please Need Help as soon as possible.


Table1
Pers_id
Spl_Event_date
Name
Address

Table2

Pers_id
Date_sal_revised
Rev_Mo_Salary

Data in Table1 is something like:
Pers_id Spl_Event_date Name address
1234 06-30-2005 adam Falls Church
5555 07-13-2006 sam Fairfax
7585 01-07-2007 moon richmond

Data in Table2 is something like:

pers_id Date_sal_revised Rev_Mo_Salary
1234 01-01-2004 4500
1234 02-25-2005 5800
1234 12-21-2006 6300
5555 12-05-2003 2700
5555 07-18-2006 3600

REQUIREMENT
Joining Table2 to Table1 on Pers_id so that Table1 carries one additional column SALARY like follows

Result Table1

Pers_id
Spl_Event_date
Name
Address
salary

CONDITION

Where selected SALARY from Table2 is EQUAL TO OR FIRST DATE'S GREATER THAN Spl_Event_date of table1

Need little help for getting the correct result set, I really appreciate your help.
Thanks in Advance.
4 REPLIES
Enthusiast

Re: Please Need Help as soon as possible.

I don't think I got the question quite right, but most likely you are looking for something like this ...

SELECT TMP1.Pers_id, TMP1.Spl_Event_Date, TMP1.Name, TMP1.Address , TMP2.Rev_Mo_Salary AS Salary
FROM
TABLE2 TMP2 INNER JOIN
(
SELECT T1.Pers_id, T1.Spl_Event_Date, MIN(T2.Date_sal_Revised) Date_Sal_revised, T1.Name, T1.Address
FROM TABLE1 T1 INNER JOIN TABLE2 T2
ON T1.Pers_id = T2.Pers_id
WHERE T2.Date_Sal_revised >= T1.Spl_Event_Date
GROUP BY 1,2, 4,5
) TMP1
ON TMP2.Pers_id = TMP1.Pers_id
AND TMP2.Date_sal_revised = TMP1.Date_sal_revised;
Enthusiast

Re: Please Need Help as soon as possible.

Thank you Joe
Enthusiast

Re: Please Need Help as soon as possible.

Try this one

select T1.EMP_ID
, T1.SPL_EVENT_DATE
, T1.NAME
, T1.ADRESS
, T2.REVISED_MONTHLY_SALARY
from TABLE_1 as T1
inner join
TABLE_2 as T2
on T1.EMP_ID = T2.EMP_ID
where exists ( select 1
from TABLE_2 as T
where T2.EMP_ID = T.EMP_ID
and T1.SPL_EVENT_DATE <= T.DATE_SAL_REVISED
having T2.DATE_SAL_REVISED = min(T.DATE_SAL_REVISED)
)
;

or

select T1.EMP_ID
, T1.SPL_EVENT_DATE
, T1.NAME
, T1.ADRESS
, T2.REVISED_MONTHLY_SALARY
from TABLE_1 as T1
inner join
TABLE_2 as T2
on T1.EMP_ID = T2.EMP_ID
and T1.SPL_EVENT_DATE <= T2.DATE_SAL_REVISED
qualify T2.DATE_SAL_REVISED = min(T2.DATE_SAL_REVISED) over(partition by T2.EMP_ID)
;
Enthusiast

Re: Please Need Help as soon as possible.

Do you have the same business problem ?

http://www.teradata.com/teradataForum/shwmessage.aspx?ForumID=1&MessageID=7157