November 3, 2006 at 7:34 am
I have a table that holds clock records of each employee. Table def (not complete)
ID int IDENTITY(1,1)
EmployeeID int,
StartTime datetime,
StopTime datetime,
ClockType int
An employee can have more than 1 clock record for a day (working on multiple jobs).
StartTime and StopTime will be on the same day.
I need a query to return the maximum employees that worked on any day within a given date range
SELECT COUNT(EmployeeID), dbo.GetDatePortion(StartTime)
FROM TimeClock
WHERE StartTime > '2006-10-01' AND startTime < '2006-11-01'
GROUP BY dbo.GetDatePortion(StartTime)
I have a function (GetDatePortion) that returns the date without time. The above query returns the total clock records for each day. How do I get the maximum employee worked on any day ?
November 3, 2006 at 7:49 am
Unoptimized :
SELECT TOP 1 COUNT(EmployeeID), dbo.GetDatePortion(StartTime)
FROM dbo.TimeClock
WHERE StartTime > '2006-10-01' AND startTime < '2006-11-01'
GROUP BY dbo.GetDatePortion(StartTime)
ORDER BY COUNT(EmployeeID) DESC
November 3, 2006 at 7:53 am
SELECT DATEADD(day, 0, DATEDIFF(day, CAST(0 as datetime) ,StartTime)) AS WorkDay
,COUNT(DISTINCT EmployeeID) AS MaxEmp
FROM TimeClock WITH (NOLOCK)
WHERE StartTime >= '20061001' AND EndTime < '20061101'
GROUP BY DATEADD(day, 0, DATEDIFF(day, CAST(0 as datetime) ,StartTime))
November 3, 2006 at 8:02 am
And the max value in all of this??
November 3, 2006 at 8:06 am
Remi, you query returns count of clock records on each date - an employee may have 10 clock records on that day but it has to return 1 for that employee.
Ken, you query worked - just a minor tweak - I need just the maximum employees on a day
SELECT TOP 1 DATEADD(day, 0, DATEDIFF(day, CAST(0 as datetime) ,StartTime)) AS WorkDay
,COUNT(DISTINCT EmployeeID) AS MaxEmp
FROM TimeClock WITH (NOLOCK)
WHERE StartTime >= '2006-10-01' AND StopTime < '2006-11-01'
GROUP BY DATEADD(day, 0, DATEDIFF(day, CAST(0 as datetime) ,StartTime))
ORDER BY 2 desc
November 3, 2006 at 8:23 am
Didn't I say that I didn't check the query?!? I was assuming it was giving the correct results but that he needed only one row.
Also I may suggest that you put the column name in the order by... the column id ordering is depreciated in sql server 2005.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply