Расчет заработной платы Sql
Привет всем там, пожалуйста, я работаю над проектом и испытываю трудности в получении желаемого результата для расчета заработной платы из нескольких таблиц. Расчет производится по отделам, и главная проблема заключается в том, что невозможно выбрать всех сотрудников в выбранном отделе, а только одного сотрудника, который ошибается. Ниже приводится то, что я пробовал. Надеюсь получить помощь в решении этой проблемы. Спасибо
Что я уже пробовал:
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(1,'EMP101','Janet Hagan','DPT103','Administration','First Margin',0,2500,0,'2017-01-28'),
(1,'EMP102','Andrews Appiah Sarkodie','DPT103','Administration','First Margin',0,2000,0,'2017-01-28'),
(1,'EMP103','Bridget Abuah','DPT102','Accounts','First Margin',0,1800,0,'2017-01-28'),
(1,'EMP106','Patrick Prince Agyei','DPT104','Information Technology and Com','First Margin',0,2000,0,'2017-01-28'),
(3,'EMP101','Janet Hagan','DPT103','Administration','Third Margin',1,2500,25,'2017-01-28'),
(3,'EMP102','Andrews Appiah Sarkodie','DPT103','Administration','Third Margin',1,2000,20,'2017-01-28'),
(3,'EMP103','Bridget Abuah','DPT102','Accounts','Third Margin',1,1800,18,'2017-01-28'),
(2,'EMP104','Sandra Antwi','DPT103','Administration','Second Margin',0.05,1600,0.80,'2017-01-28'),
(2,'EMP105','Success Abenaa Gyasiwaa','DPT101','Marketing','Second Margin',0.05,1500,0.75,'2017-01-28'),
(3,'EMP106','Patrick Prince Agyei','DPT104','Information Technology and Com','Third Margin',1,2000,20,'2017-01-28'),
(2,'EMP107','Nancy Kuffour','DPT101','Marketing','Second Margin',0.05,1570,0.785,'2017-01-28'),
(1,'EMP108','George Mensah','DPT105','Sales','First Margin',0,700,0,'2017-01-28'),
(2,'EMP109','Vincent Arthur','DPT102','Accounts','Second Margin',0.05,1200,0.60,'2017-01-28'),
(1,'EMP110','Kwaku Boakye','DPT105','Sales','First Margin',0,600,0,'2017-01-28'),
(1,'EMP111','Akwasi Sarpong','DPT105','Sales','First Margin',0,700,0,'2017-01-28'),
(1,'EMP112','Rose Asare','DPT103','Sales','First Margin',0,800,0,'2017-01-28'),
(1,'EMP113','Collins Kyei Baffour','DPT104','Information Technology and Com','First Margin',0,800,0,'2017-01-28'),
(1,'EMP114','Kwabena Ofori','DPT105','Sales','First Margin',0,900,0,'2017-01-28'),
(1,'EMP115','Doris Asamoah','DPT103','Administration','First Margin',0,600,0,'2017-01-28'),
(1,'EMP116','Akwasi Appiah','DPT103','Administration','First Margin',0,650,0,'2017-01-28'),
(2,'EMP117','Adwoa Konadu','DPT104','Information Technology and Com','Second Margin',0.05,1600,0.80,'2017-01-28'),
(2,'EMP118','Jerome Dotsey','DPT102','Accounts','Second Margin',0.05,1500,0.75,'2017-01-28')
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'
После запуска вышеприведенного скрипта это то, что я получаю. Он повторяет одно имя сотрудника для tweenty-четыре раза вместо того, чтобы появиться один раз, и оставляет остальных сотрудников в отделе.
EMP102 Andrews Appiah Sarkodie DPT103 Administration Deputy MD 2000 00 190 00 0 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 0 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 1975 00 2265 00 2017-01-28 00:00:00.000 EMP102 Andrews Appiah Sarkodie DPT103 Administration Deputy MD 2000 00 190 00 0 00 January 2017 2100 00 1962 50 2252 50 2017-01-28 00:00:00.000 EMP102 Andrews Appiah Sarkodie DPT103 Administration Deputy MD 2000 00 190 00 0 00 January 2017 2100 00 1962 50 2252 50 2017-01-28 00:00:00.000