All Forums Tools
pritpal 5 posts Joined 03/06
17 Apr 2007
Please Need Help as soon as possible.

Table1Pers_idSpl_Event_dateNameAddressTable2Pers_idDat e_sal_revisedRev_Mo_SalaryData in Table1 is something like:Pers_id Spl_Event_date Name address1234 06-30-2005 adam Falls Church5555 07-13-2006 sam Fairfax7585 01-07-2007 moon richmond Data in Table2 is something like:pers_id Date_sal_revised Rev_Mo_Salary1234 01-01-2004 4500 1234 02-25-2005 5800 1234 12-21-2006 63005555 12-05-2003 2700 5555 07-18-2006 3600REQUIREMENTJoining Table2 to Table1 on Pers_id so that Table1 carries one additional column SALARY like followsResult Table1Pers_idSpl_Event_dateNameAddress salaryCONDITIONWhere selected SALARY from Table2 is EQUAL TO OR FIRST DATE'S GREATER THAN Spl_Event_date of table1Need little help for getting the correct result set, I really appreciate your help.Thanks in Advance.

joedsilva 505 posts Joined 07/05
18 Apr 2007

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 SalaryFROM TABLE2 TMP2 INNER JOIN(SELECT T1.Pers_id, T1.Spl_Event_Date, MIN(T2.Date_sal_Revised) Date_Sal_revised, T1.Name, T1.AddressFROM TABLE1 T1 INNER JOIN TABLE2 T2ON T1.Pers_id = T2.Pers_idWHERE T2.Date_Sal_revised >= T1.Spl_Event_DateGROUP BY 1,2, 4,5) TMP1ON TMP2.Pers_id = TMP1.Pers_idAND TMP2.Date_sal_revised = TMP1.Date_sal_revised;

pritpal 5 posts Joined 03/06
18 Apr 2007

Thank you Joe

teratarun 7 posts Joined 08/06
18 Apr 2007

Try this oneselect T1.EMP_ID, T1.SPL_EVENT_DATE, T1.NAME, T1.ADRESS, T2.REVISED_MONTHLY_SALARYfrom TABLE_1 as T1inner joinTABLE_2 as T2on T1.EMP_ID = T2.EMP_IDwhere exists ( select 1from TABLE_2 as Twhere T2.EMP_ID = T.EMP_IDand T1.SPL_EVENT_DATE <= T.DATE_SAL_REVISEDhaving 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_SALARYfrom TABLE_1 as T1inner joinTABLE_2 as T2on T1.EMP_ID = T2.EMP_IDand T1.SPL_EVENT_DATE <= T2.DATE_SAL_REVISEDqualify T2.DATE_SAL_REVISED = min(T2.DATE_SAL_REVISED) over(partition by T2.EMP_ID);

al1_24 39 posts Joined 11/04
18 Apr 2007

Do you have the same business problem ?http://www.teradata.com/teradataForum/shwmessage.aspx?ForumID=1&MessageID=7157

You must sign in to leave a comment.