How to write stored procedure for this problem

Question

Hello, I have 2 tables. Table1: Emp_Profile Emp_Code Emp_Name Rank Salary E1 A 1 40K E2 B 2 30K E3 C 3 25K E4 D 3 25K Table2: Emp_Promotion Emp_Code Rank E2 1 E3 2 I need all emp. details but condition is: If employee is promoted, then Rank will comes from Emp_Promotion table. Output looks like: Emp_Code Emp_Name Rank Salary E1 A 1 40K E2 B 1 30K E3 C 2 25K E4 D 3 25K Please help.

Ankit Shukla · Answer

Hello Sir, Please help on this scenario. This is little bit different from previous scenario. Table1: Emp_Profile Emp_Code Emp_Name Rank Salary E1 A 1 40K E2 B 2 30K E3 C 3 25K E4 D 3 25K Table2: Emp_Promotion Emp_Code Rank Date E2 1 2009-01-01 E3 2 2009-01-01 E3 1 2010-01-01 Condition : If employee is promoted, then top 1 Rank will comes from Emp_Promotion table order by Date desc. Output looks like: Emp_Code Emp_Name Rank Salary E1 A 1 40K E2 B 1 30K E3 C 1 25K E4 D 3 25K Thanks in advance.

Jignesh Kumar · Answer

Hi Ankit, You should have required data in all table where you are using inner join. Else you want get data. Check you join condition field exists in all table

Ankit Shukla · Answer

Thank you Sir. its work when I use these 2 tables. But When I use some more tables, It doesn't work. My procedure query is: Select isnull(P.DESIGNATION_CD, E.JOINING_RANK_ID) as Rank,E.Gpf_No, Ca.Cader as cader, E.FIRST_NAME + ' ' + E.MIDDLE_NAME + ' ' + E.LAST_NAME as Name , E.REL_FIRST_NAME + ' ' + E.REL_MIDDLE_NAME + ' ' + E.REL_LAST_NAME as Father,C.CATEGORY, d.DISTRICT as homedistrict,O.OFFICE_NAME as Posting_Place, E.DOB,E.ORDER_NO , 'ptc basic training' as TrainingType, T.Trainning_name, 'TrainingDistrict' as TrainingDistrict ,T.Start_Trainning,T.End_Trainning, T.Secured_mark,T.Total_Mark,T.Remark_Training from T_HR_Employee_Profile E inner join T_HR_TRAINING_DETAIL T on E.CANDIDATE_ID = T.Candidate_id inner join HRMS_M_CATEGORY_MAPPING C on C.CATEGORY_CD = E.CATEGORY_CD inner join m_district d on d.DISTRICT_CD = E.PERMANENT_ADDR_DISTRCT_CD inner join M_Cader Ca on Ca.Cader_ID = E.Cadre_CD inner join m_office_types O on O.OFFICE_CD = E.Place_Of_Posting left join t_employee_promotion_pis P on E.Gpf_No = P.GPF_No

Jignesh Kumar · Answer

Hi, Use this Store procedure, create procedure usp_GetEmployeeDetails As Begin select ep.Emp_Code,ep.Emp_Name, isnull (epr.rank, ep.Rank) as Rank,ep.Salary from Emp_Profile ep left join Emp_Promotion epr on ep.Emp_Code = epr.Emp_Code End - To Run SP exec usp_GetEmployeeDetails