December 3, 2011 at 1:27 am
SELECt Emp.EmpCode ,Emp.Name ,Dept.Name as Depart ,Desig.Name as Desig ,
Sum(isNull(OverT.OverTH,0))as Hourss ,Sum(isNull(OverT.OverTM,0)) as Mins,
COUNT(DISTINCT CONVERT(DATE, TimeIn)) AS DaysWorked,
(COUNT(DISTINCT CONVERT(DATE, TimeIn)) * ( Emp.BasicSal / 30 )) AS MonthSal,
((Emp.BasicSal / 30 )/9)* sum(isnull(OverT.OverTH,0)) AS OverTWage,
((COUNT(DISTINCT CONVERT(DATE, TimeIn)) * ( Emp.BasicSal / 30)) + (( Emp.BasicSal /30 )/9)* sum(isnull(OverT.OverTH,0))) AS TotalSal
FROM Emp inner JOIN Attend ON Emp.EmpCode = Attend.EmpCode
left outer join Dept on Emp.DeptCode=Dept.DeptCode
left outer join Desig on Emp.DesigCode=Desig.DesigCode
left outer join OverT on OverT.EmpCode=Emp.EmpCode
WHERE CONVERT(DATE, TimeIn) BETWEEN '2011-10-01' AND '2011-10-31'
And CONVERT(Date,OverT.AttendDate) between '2011-10-01' And '2011-10-31'
GROUP BY Emp.EmpCode, Emp.Basicsal ,Emp.Name ,Dept.Name ,Desig.Name
This is the complete query, OverT is the OverTime Table from which i am getting employees overtime hours and minutes, i dont want to use the sum function, because it sum the results and i dont want it.. i want a list of result grouped only by their emp codes., and overtime hours displayed infront of employees only for the specific dates..
December 3, 2011 at 2:53 am
You may have noticed that (by the time I'm writing this reply) almost 50 people did read your post. But noone answered.
The main reason most probably is the incomplete information.
In order to provide the data we need please read and follow the advice given in the first link in my signature. I'm sure you'll see some code samples to sole the issue almost immediately after posting ready to use information.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply