supportsvc
Technical User
Need help on how to determine the unique number of employees in each month of the quarter by the 12th of the month in the previous quarter.
** How many employees worked by the 12th of each month in the previous quarter.
So tx number of employees each month by the 12th of the month of the previous quarterter, which are Jan-Mar.
NOTE: in Jan. the previous quarter (4th quarter) is in the prior year.
Current issue: The count function produces the number of employees with a check since the table it's from is a check history table.
I have this. However it is counting the employee for every checkdate.
Is a Distinct Count needed here and if so how do I use it with the above?
I've seen some posts on Distinct, but can't figure it out here.
** How many employees worked by the 12th of each month in the previous quarter.
So tx number of employees each month by the 12th of the month of the previous quarterter, which are Jan-Mar.
NOTE: in Jan. the previous quarter (4th quarter) is in the prior year.
Current issue: The count function produces the number of employees with a check since the table it's from is a check history table.
I have this. However it is counting the employee for every checkdate.
Code:
SELECT tblCheckHistory.CompanyCode, tblCheckHistory.DepartmentNo, tblCheckHistory.EmployeeNo, Count(IIf(Month([CheckDate])=Month(Date())-3,[EmployeeNo],0)) AS Mth1, Count(IIf(Month([CheckDate])=Month(Date())-2,[EmployeeNo],0)) AS Mth2, Count(IIf(Month([CheckDate])=Month(Date())-1,[EmployeeNo],0)) AS Mth3, Count(IIf(DatePart("q",Date())-1,[EmployeeNo],0)) AS Qtr INTO tblCountEmployeesByQtr
FROM tblCheckHistory
GROUP BY tblCheckHistory.CompanyCode, tblCheckHistory.DepartmentNo, tblCheckHistory.EmployeeNo;
Is a Distinct Count needed here and if so how do I use it with the above?
I've seen some posts on Distinct, but can't figure it out here.