May 14, 2009 at 5:01 am
Select DISTINCT ESD.EmployeeIDFK, RIGHT('0' + CAST(EM.EmpCodePrefix AS varchar(2)), 2) + RIGHT('0' + CAST(EM.CompanyIdFK AS varchar(2)), 2)
+ RIGHT('000' + CAST(EM.EmpCodePostfix AS varchar(8)), 4) AS EmpCode, EM.EmpFirstName + ' ' + EM.EmpLastName AS EmpName,
SUM(Case When ESD.SalaryStructureIDFK = 1 AND ESD.Month = @Month and ESD.Year = @Year then ESD.StructureAmount else 0.0 END) BasicSalary,
SUM(Case When ESD.SalaryStructureIDFK = 2 AND ESD.Month = @Month and ESD.Year = @Year then ESD.StructureAmount else 0.0 END) Medical,
SUM(Case When ESD.SalaryStructureIDFK = 3 AND ESD.Month = @Month and ESD.Year = @Year then ESD.StructureAmount else 0.0 END) Convence,
SUM(Case When ESD.SalaryStructureIDFK = 4 AND ESD.Month = @Month and ESD.Year = @Year then ESD.StructureAmount else 0.0 END) HouseRent,
SUM(Case When ESD.SalaryStructureIDFK = 5 AND ESD.Month = @Month and ESD.Year = @Year then ESD.StructureAmount else 0.0 END) Bonus,
SUM(Case When ESD.DeductionIDFK = 1 AND ESD.Month = @Month AND ESD.Year = @Year then ESD.DeductionAmount else 0.0 END) IncomeTax,
SUM(Case When ESD.DeductionIDFK = 2 AND ESD.Month = @Month AND ESD.Year = @Year then ESD.DeductionAmount else 0.0 END) Loan,
SUM(Case When ESD.DeductionIDFK = 3 AND ESD.Month = @Month AND ESD.Year = @Year then ESD.DeductionAmount else 0.0 END) LeaveFine,
SUM(Case When ESD.DeductionIDFK = 4 AND ESD.Month = @Month AND ESD.Year = @Year then ESD.DeductionAmount else 0.0 END) SpecialFine,
SUM(Case When ESD.DeductionIDFK = 5 AND ESD.Month = @Month AND ESD.Year = @Year then ESD.DeductionAmount else 0.0 END) PFund,
SUM(Case When ESD.Month = @Month AND ESD.Year = @Year Then ESD.StructureAmount else 0.0 END) GrossSalary,
SUM(CASE When ESD.Month = @Month AND ESD.Year = @Year Then ESD.DeductionAmount Else 0.0 END) TotalDeductions,
SUM(Case When ESD.Month = @Month AND ESD.Year = @Year Then ESD.StructureAmount else 0.0 END) -
SUM(CASE When ESD.Month = @Month AND ESD.Year = @Year Then ESD.DeductionAmount Else 0.0 END) TotalSalary
From Emp_SalaryDetail AS ESD
LEFT OUTER JOIN Emp_EmployeeMain AS EM on EM.EmployeeID = ESD.EmployeeIDFK
Left Outer Join Emp_SalaryStructured AS ESS on ESS.StructuredID = ESD.SalaryStructureIDFK
left Outer Join Emp_SalaryDeduction AS ES on ES.DeductionID = ESD.DeductionIDFK
Where ESD.Month = @Month and ESD.Year = @Year
Group by ESD.EmployeeIDFK, RIGHT('0' + CAST(EM.EmpCodePrefix AS varchar(2)), 2) + RIGHT('0' + CAST(EM.CompanyIdFK AS varchar(2)), 2)
+ RIGHT('000' + CAST(EM.EmpCodePostfix AS varchar(8)), 4), EM.EmpFirstName + ' ' + EM.EmpLastName
I am Doing this.. this is absolutely right..... My problem is solved but again i am facing two problems Which are
"ESD.SalaryStructuredID = 1" should not equal to "1" but it takes it from Table Emp_SalaryStructured same case with salary deduction.
May 14, 2009 at 5:22 am
Please dear check this if you have any problem regarding this then i can solve that.... but please help me to solve this big problem.. Thanks in advance
May 14, 2009 at 5:22 am
Please dear check this if you have any problem regarding this then i can solve that.... but please help me to solve this big problem.. Thanks in advance
May 14, 2009 at 5:23 am
Please dear check this if you have any problem regarding this then i can solve that.... but please help me to solve this big problem.. Thanks in advance
May 14, 2009 at 9:10 pm
hi,
show you tables
1)Emp_SalaryDetail,
2)Emp_EmployeeMain,
3)Emp_SalaryStructured,
4)Emp_SalaryDeduction
with sample data
ARUN SAS
May 15, 2009 at 12:34 am
Dear brother thanks a tons.
I have solved this problm by creating a new c# class which generates a table which return my required Data format.. So thank you very much if i have again any difficulty i will definalty put on this forum..
May 15, 2009 at 12:34 am
Dear brother thanks a tons.
I have solved this problm by creating a new c# class which generates a table which return my required Data format.. So thank you very much if i have again any difficulty i will definalty put on this forum..
May 15, 2009 at 12:34 am
Dear brother thanks a tons.
I have solved this problm by creating a new c# class which generates a table which return my required Data format.. So thank you very much if i have again any difficulty i will definalty put on this forum..
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply