May 14, 2009 at 2:00 am
Dear Brother's I have Table in which records are in following manners..
EmployeeID -- StructureID -- StructureAmount -- DeductionID -- DeductionAmount
01 01 13500 01 70.0
01 02 1500 02 0.0
01 03 1230 03 500
02 01 14500 01 0.0
02 02 1400 02 405
02 03 1100 03 0.0
. . . . .
. . . . .
and So on.. Where StructureID 01 = Basic Salary, 02 Medical, 03 Connvence and DeductionID 01 = IncomeTax, 02 = Leave Fine, 03 = Loan,
Now I want to write a stored procedure which show Data like this....
EmployeeID -- Basic Salary -- Medical -- Connvence -- IncomeTax -- LeaveFine -- Loan -- TotalSalary
01 13500 1500 1230 70.0 0.0 500 15660
02 14500 1400 1100 0.0 405 0.0 16595
. . . . . . . .
and so on,
If any Brother have idea how to slove this qurey then please share it.. I need this very much....
May 14, 2009 at 2:15 am
hi,
very basic piviting code,
try this
create table #temp
(
EmployeeID varchar(2),
StructureID varchar(2),
StructureAmount int,
DeductionID varchar(2),
DeductionAmount float
)
insert into #temp
select '01', '01', 13500, '01', 70.0
union all
select'01', '02', 1500, '02', 0.0
union all
select'01', '03', 1230, '03' ,500
union all
select'02', '01', 14500,'01' ,0.0
union all
select'02', '02', 1400, '02', 405
union all
select'02', '03', 1100, '03', 0.0
select *,(([Basic Salary]+[Medical]+[Connvence])-([IncomeTax]+[Leave Fine]+[Loan]))as [TotalSalary] from
(
select EmployeeID,
sum(case when StructureID = 01 then StructureAmount else 0 end)as [Basic Salary],
sum(case when StructureID = 02 then StructureAmount else 0 end)as [Medical],
sum(case when StructureID = 03 then StructureAmount else 0 end)as [Connvence],
sum(case when DeductionID = 01 then DeductionAmount else 0 end)as [IncomeTax],
sum(case when DeductionID = 02 then DeductionAmount else 0 end)as [Leave Fine],
sum(case when DeductionID = 03 then DeductionAmount else 0 end)as [Loan]
from #temp
group by EmployeeID
)as X
RESULT
EmployeeIDBasic SalaryMedicalConnvenceIncomeTaxLeave FineLoanTotalSalary
01135001500123070.00.0500.015660.0
0214500140011000.0405.00.016595.0
ARUN SAS
May 14, 2009 at 2:47 am
this dear, after try i will send you response... Again thanks.
May 14, 2009 at 3:07 am
this is not working dear,
It gives only totalsalary and nothing else...... Do you have an other idea...
I have table named Emp_SalaryDetail Which contains following coloumns.
EmployeeidFk -- SalaryStructuredID -- SalaryStructureAmount -- SalaryDeductionID -- SalaryDeductionAmount
01 -- 01 -- 13500 -- 01 -- 70
01 -- 02 -- 1250 -- 02 -- 0.0
01 -- 03 -- 00 -- 03 -- 500
and so on... Please help me..
May 14, 2009 at 3:25 am
waqarlaghari (5/14/2009)
It gives only totalsalary and nothing else...... Do you have an other idea...
Hi,
once againg see/study the above simple pivot code.
ARUN SAS
May 14, 2009 at 3:50 am
I have check it not once but a lot of time it is given me the right result. Please brother you check it first may be you were worng... I am waiting for your response... Thanks in advance
May 14, 2009 at 3:58 am
waqarlaghari (5/14/2009)
Now I want to write a stored procedure which show Data like this....EmployeeID -- Basic Salary -- Medical -- Connvence -- IncomeTax -- LeaveFine -- Loan -- TotalSalary
01 13500 1500 1230 70.0 0.0 500 15660
02 14500 1400 1100 0.0 405 0.0 16595
. . . . .
Hi,
so you need the output in what fromat?
ARUN SAS
May 14, 2009 at 4:00 am
arun.sas (5/14/2009)
waqarlaghari (5/14/2009)
Now I want to write a stored procedure which show Data like this....EmployeeID -- Basic Salary -- Medical -- Connvence -- IncomeTax -- LeaveFine -- Loan -- TotalSalary
01 13500 1500 1230 70.0 0.0 500 15660
02 14500 1400 1100 0.0 405 0.0 16595
. . . . .
Hi,
so you need the output in what fromat?
ARUN SAS
EmployeeID -- Basic Salary -- Medical -- Connvence -- IncomeTax -- LeaveFine -- Loan -- TotalSalary
01 13500 1500 1230 70.0 0.0 500 15660
02 14500 1400 1100 0.0 405 0.0 16595
. . . . .
In this format for Report...
May 14, 2009 at 4:10 am
EmployeeID -- Basic Salary -- Medical -- Connvence -- IncomeTax -- LeaveFine -- Loan -- TotalSalary
01 13500 1500 1230 70.0 0.0 500 15660
02 14500 1400 1100 0.0 405 0.0 16595
. . . . .
In this format for Report...
Hi,
is any differance with my output format?
EmployeeIDBasic SalaryMedicalConnvenceIncomeTaxLeave FineLoanTotalSalary
1135001500123070050015660
214500140011000405016595
ARUN SAS
May 14, 2009 at 4:19 am
Yah there is difference, becuse your output shows like this,
TotalSalary
1233
124
12
122
.
.
like so on.... this is not going to show the whole information dear which i need.. Now i hope you have understand...
May 14, 2009 at 4:19 am
Yah there is difference, becuse your output shows like this,
TotalSalary
1233
124
12
122
.
.
like so on.... this is not going to show the whole information dear which i need.. Now i hope you have understand...
May 14, 2009 at 4:19 am
Yah there is difference, becuse your output shows like this,
TotalSalary
1233
124
12
122
.
.
like so on.... this is not going to show the whole information dear which i need.. Now i hope you have understand...
May 14, 2009 at 4:27 am
Hi,
Then how you are calculate the TotalSalary?
ARUN SAS
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: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.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply