Refer the below test query.
DECLARE @TblEmployee AS TABLE(Employeeid INT,EName VARCHAR(20),ECode INT,ESalary INT)
INSERT INTO @TblEmployee VALUES(1,'A',1,6000)
INSERT INTO @TblEmployee VALUES(2,'B',1,1100)
INSERT INTO @TblEmployee VALUES(3,'C',1,1200)
INSERT INTO @TblEmployee VALUES(4,'D',1,1300)
DECLARE @TblEmployeeAttendance AS TABLE(Employeeid INT,Attendancedate DATETIME)
INSERT INTO @TblEmployeeAttendance VALUES(1,GETDATE()-1)
INSERT INTO @TblEmployeeAttendance VALUES(2,GETDATE()-2)
INSERT INTO @TblEmployeeAttendance VALUES(2,GETDATE()-3)
INSERT INTO @TblEmployeeAttendance VALUES(3,GETDATE()-3)
INSERT INTO @TblEmployeeAttendance VALUES(1,GETDATE())
INSERT INTO @TblEmployeeAttendance VALUES(1,GETDATE())
INSERT INTO @TblEmployeeAttendance VALUES(1,GETDATE())
INSERT INTO @TblEmployeeAttendance VALUES(1,GETDATE())
INSERT INTO @TblEmployeeAttendance VALUES(1,GETDATE())
INSERT INTO @TblEmployeeAttendance VALUES(1,GETDATE())
SELECT ROW_NUMBER() OVER (ORDER BY TblEmployee.Employeeid ASC) AS RowNo,
TblEmployee.Employeeid,TblEmployee.EName,TblEmployee.ECode,
(TblEmployee.ESalary * (COUNT(TblEmployeeAttendance.Attendancedate))/30)AS Salary
FROM @TblEmployeeAttendance TblEmployeeAttendance
INNER JOIN @TblEmployee TblEmployee
ON TblEmployee.Employeeid = TblEmployeeAttendance.Employeeid
GROUP BY TblEmployee.Employeeid,TblEmployee.EName,TblEmployee.ECode,TblEmployee.ESalary
OutPut
| RowNo |
Employeeid |
EName |
ECode |
Salary |
| 1 |
1 |
A |
1 |
1400 |
| 2 |
2 |
B |
1 |
73 |
| 3 |
3 |
C |
1 |
40 |
