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
Submit an article or tip