Click here to Skip to main content
14,733,513 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello to everyone out there, please am working on a project and am having difficulties in getting the desire result for salary calculation from multiple tables. The calculation is by department and the main problem is that is not able to select all the employee within the selected department but only select one employee which is wrong. Below is what i have tried. Hope to get help in solving this problem. Thanks

What I have tried:

Declare @EmployeeDetails Table(EmployeeID	VarChar(10),EmployeeName VarChar(50),DepartmentID	VarChar(10),Department	VarChar(50),
								Designation	VarChar(50),BasicSalary	Money)
Declare @t_tmpAllowanceReport Table(EmployeeID	VarChar(10),AllowanceDescription	VarChar(30),Amount Money)
Declare @t_tmpDeductionsReport Table(EmployeeID	VarChar(10),DeductionType	VarChar(30),AmountDeducted	Money)
Declare @MonthlyOverTimeSummary Table(EmployeeID	VarChar(10),EmployeeName VarChar(50),SalaryMonth	VarChar(15),SalaryYear VarChar(5),SalaryFrequency VarChar(25),OverTime Money)
Declare @TaxDeductionHistory Table(TaxRateId SmallInt,EmployeeID	VarChar(10),EmployeeName VarChar(50),DepartmentID VarChar(10),Department VarChar(60),
									SalaryMargin VarChar(25),TaxRate Float,BasicSalary Money,TaxAmount Money,TransactionDate DateTime)

Insert Into @EmployeeDetails
Values('EMP101','Janet Hagan','DPT103','Administration','Managing Director',2500),
('EMP102','Andrews Appiah Sarkodie','DPT103','Administration','Deputy MD',2000),
('EMP103','Bridget Abuah','DPT102','Accounts','Department Head',1800),
('EMP104','Sandra Antwi','DPT103','Administration','Secretary',1600),
('EMP105','Success Abenaa Gyasiwaa','DPT101','Marketing','Secretary',1500),
('EMP106','Patrick Prince Agyei','DPT104','Information Technology and Com','Department Head',2000),
('EMP107','Nancy Kuffour','DPT101','Marketing','Deputy MD',1570),
('EMP108','George Mensah','DPT105','Sales','Sales Personal',7000),
('EMP109','Vincent Arthur','DPT102','Accounts','Sales Personal',1200),
('EMP110','Kwaku Boakye','DPT105','Sales','Sales Personal',600),
('EMP111','Akwasi Sarpong','DPT105','Sales','SalesPersonal',700),
('EMP112','Rose Asare','DPT103','Sales','Sales Personal',800),
('EMP113','Collins Kyei Baffour','DPT104','Information Technology and Com','Supervisor',800),
('EMP114','Kwabena Ofori','DPT105','Sales','Sales Personal',900),
('EMP115','Doris Asamoah','DPT103','Administration','Cleaner',600),
('EMP116','Akwasi Appiah','DPT103','Administration','Messenger',650),
('EMP117','Adwoa Konadu','DPT104','Information Technology and Com','Supervisor',1600),
('EMP118','Jerome Dotsey','DPT102','Accounts','Supervisor',1500)

Insert Into @t_tmpAllowanceReport
Values('EMP101','Rent',100),
('EMP102','Rent',100),
('EMP103','Rent',100),
('EMP104','Rent',100),
('EMP105','Rent',100),
('EMP106','Rent',100),
('EMP107','Rent',100),
('EMP108','Rent',100),
('EMP109','Rent',100),
('EMP110','Rent',100),
('EMP111','Rent',100),
('EMP101','Fuel',230),
('EMP108','House',70),
('EMP109','House',70),
('EMP104','New expansion',300),
('EMP101','Rent',100),
('EMP102','Rent',100),
('EMP103','Rent',100),
('EMP104','Rent',100),
('EMP105','Rent',100),
('EMP106','Rent',100),
('EMP107','Rent',100),
('EMP108','Rent',100),
('EMP109','Rent',100),
('EMP110','Rent',100),
('EMP111','Rent',100),
('EMP101','Fuel',230),
('EMP108','House',70),
('EMP109','House',70),
('EMP104','New expansion',300)

Insert Into @t_tmpDeductionsReport
Values('EMP102','SSNIT',25),
('EMP102','Education',37.50),
('EMP102','Health',37.50),
('EMP110','SSNIT',23),
('EMP110','Education',34.50),
('EMP110','Health',34.50),
('EMP111','SSNIT',17),
('EMP111','Education',25.50),
('EMP111','Health',25.50),
('EMP102','SSNIT',25),
('EMP102','Education',37.50),
('EMP102','Health',37.50),
('EMP110','SSNIT',23),
('EMP110','Education',34.50),
('EMP110','Health',34.50),
('EMP111','SSNIT',17),
('EMP111','Education',25.50),
('EMP111','Health',25.50)

Insert Into @MonthlyOverTimeSummary
Values('EMP102','Andrews Appiah Sarkodie','January','2017','Monthly',190),
('EMP110','Bridget Abuah','January','2017','Monthly',103),
('EMP108','Collins Kyei Baffour','January','2017','Monthly',76),
('EMP104','George Mensah','January','2017','Monthly',102),
('EMP107','Isaac Danso','January','2017','Monthly',72),
('EMP109','Linda Nkrumah','January','2017','Monthly',536),
('EMP103','Nancy Kuffour','January','2017','Monthly',153),
('EMP101','Samuel Osei Banahene','January','2017','Monthly',152),
('EMP105','Success Abena Gyasiwaa','January','2017','Monthly',144),
('EMP106','Vincent Arthur','January','2017','Monthly',38)

Insert Into @TaxDeductionHistory
Values(3,'EMP101','Janet Hagan','DPT103','Administration','Third Margin',1,2500,25,'28 JAN 2017'),
(3,'EMP102','Andrews Appiah Sarkodie','DPT103','Administration','Third Margin',1,2000,20,'28 JAN 2017'),
(3,'EMP103','Bridget Abuah','DPT102','Accounts','Third Margin',1,1800,18,'28 JAN 2017'),
(2,'EMP104','Sandra Antwi','DPT103','Administration','Second Margin',0.05,1600,0.8,'28 JAN 2017'),
(2,'EMP105','Success Abenaa Gyasiwaa','DPT101','Marketing','Second Margin',0.05,1500,0.75,'28 JAN 2017'),
(3,'EMP106','Patrick Prince Agyei','DPT104','Information Technology and Com','Third Margin',1,2000,20,'28 JAN 2017'),
(2,'EMP107','Nancy Kuffour','DPT101','Marketing','Second Margin',0.05,1570,0.785,'28 JAN 2017'),
(1,'EMP108','George Mensah','DPT105','Sales','First Margin',0,700,0,'28 JAN 2017'),
(2,'EMP109','Vincent Arthur','DPT102','Accounts','Second Margin',0.05,1200,0.6,'28 JAN 2017'),
(1,'EMP110','Kwaku Boakye','DPT105','Sales','First Margin',0,600,0,'28 JAN 2017'),
(1,'EMP111','Akwasi Sarpong','DPT105','Sales','First Margin',0,700,0,'28 JAN 2017'),
(1,'EMP112','Rose Asare','DPT103','Sales','First Margin',0,800,0,'28 JAN 2017'),
(1,'EMP113','Collins Kyei Baffour','DPT104','Information Technology and Com','First Margin',0,800,0,'28 JAN 2017'),
(1,'EMP114','Kwabena Ofori','DPT105','Sales','First Margin',0,900,0,'28 JAN 2017'),
(1,'EMP115','Doris Asamoah','DPT103','Administration','First Margin',0,600,0,'28 JAN 2017'),
(1,'EMP116','Akwasi Appiah','DPT103','Administration','First Margin',0,650,0,'28 JAN 2017'),
(2,'EMP117','Adwoa Konadu','DPT104','Information Technology and Com','Second Margin',0.05,1600,0.8,'28 JAN 2017'),
(2,'EMP118','Jerome Dotsey','DPT102','Accounts','Second Margin',0.05,1500,0.75,'28 JAN 2017')

Select 	e.EmployeeID,
	e.EmployeeName,
	e.DepartmentID,
	e.Department,
	e.Designation,
	e.BasicSalary,
	o.OverTime,
	T.TaxAmount,
	o.SalaryMonth,
	o.SalaryYear,
	(e.BasicSalary + a.Amount),
	(e.BasicSalary - d.AmountDeducted),
	((e.BasicSalary + a.Amount + o.OverTime) - d.AmountDeducted),
	T.TransactionDate
From @EmployeeDetails e
Join @t_tmpDeductionsReport d
On e.EmployeeID = d.EmployeeID
Join @t_tmpAllowanceReport a
On e.EmployeeID = a.EmployeeID
Join @MonthlyOverTimeSummary o
On e.EmployeeId = o.EmployeeID
Join @TaxDeductionHistory T
On e.EmployeeID = T.EmployeeID
Where e.DepartmentID = 'DPT103'

I have remove the duplicate records and am still not getting it correct. Now it repeat one employee name for twelve times instead of appearing once and leave the rest of employee in the department.
EMP102	Andrews Appiah Sarkodie	DPT103	Administration	Deputy MD	2000 00	190 00	20 00	January	2017	2100 00	1975 00	2265 00	2017-01-28 00:00:00.000
EMP102	Andrews Appiah Sarkodie	DPT103	Administration	Deputy MD	2000 00	190 00	20 00	January	2017	2100 00	1975 00	2265 00	2017-01-28 00:00:00.000
EMP102	Andrews Appiah Sarkodie	DPT103	Administration	Deputy MD	2000 00	190 00	20 00	January	2017	2100 00	1962 50	2252 50	2017-01-28 00:00:00.000
......
......
......10	EMP102	Andrews Appiah Sarkodie	DPT103	Administration	Deputy MD	2000 00	190 00	20 00	January	2017	2100 00	1962 50	2252 50	2017-01-28 00:00:00.000
11	EMP102	Andrews Appiah Sarkodie	DPT103	Administration	Deputy MD	2000 00	190 00	20 00	January	2017	2100 00	1962 50	2252 50	2017-01-28 00:00:00.000
12	EMP102	Andrews Appiah Sarkodie	DPT103	Administration	Deputy MD	2000 00	190 00	20 00	January	2017	2100 00	1962 50	2252 50	2017-01-28 00:00:00.000
Posted
Updated 20-Nov-20 5:44am
v3

1 solution

You have multiple records, in multiple tables, for employee EMP102; so you're doing a cartesion join; and since EMP102 seems to have hit every table, that's why only that one seems to qualify; the others were excluded.

Group sum "dependents" first so you're only joining singles.
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900