Stack Exchange
SQL 2008 - I need to count number of rows per employee in given time frame
I have a table that has employee's daily totals with start date, I need to look back 3 months to see how many days an employee has worked.
Here is my sql query:
    SELECT
    EMPNO,
    CONVERT(VARCHAR(10), STARTDATE,101),
    ROW_NUMBER() OVER (ORDER BY PERSONNUM) AS 'ROWCOUNT'
    FROM EMPLOYEE
    WHERE STARTDATE BETWENN DATEADD(month, -3, GETDATE()) and GETDATE()
    GROUP BY EMPNO,STARTDATE
    ORDER BY EMPNO

Result
   EMPNO      STARTDATE               ROWCOUNT
   TEST108    09/13/2013              1
   TEST108    09/16/2013              2
   TEST108    09/17/2013              3
   TEST108    09/19/2013              4
   TEST109    09/04/2013              5
   TEST109    09/05/2013              6
   TEST109    09/06/2013              7
   TEST110    09/03/2013              9
   TEST110    09/04/2013             10
   TEST110    09/05/2013             11

Desired Result
   EMPNO           ROWCOUNT
   TEST108         4
   TEST109         3
   TEST110         3

Thank you,

Can there be multiple rows for one employee with the same STARTDATE, or why do you group over EMPNO, STARTDATE?

You can use this CTE with ROW_NUMBER and COUNT(*)OVER:
WITH CTE AS
(
    SELECT EMPNO, STARTDATE, 
           RN = ROW_NUMBER() OVER (PARTITION BY EMPNO ORDER BY STARTDATE DESC),
           [ROWCOUNT] = COUNT(*) OVER (PARTITION BY EMPNO)
    FROM dbo.EMPLOYEE
    WHERE STARTDATE BETWEEN DATEADD(month, -3, GETDATE()) and GETDATE()
)
SELECT EMPNO, STARTDATE, [ROWCOUNT]
FROM CTE
WHERE RN = 1

DEMO
The ROW_NUMBER returns a number for every row in a partiton(similar to GROUP BY EMPNO) and allows to select all columns without needing to aggregate all. The COUNT(*)OVER returns the total-count of rows for each partition, so that what you want. I use the ROW_NUMBER just to remove the duplicates. 

Can't you use a count?
SELECT EMPNO, COUNT(EMPNO) AS [ROWCOUNT]
FROM EMPLOYEE
WHERE STARTDATE BETWEEN DATEADD(month, -3, GETDATE()) and GETDATE()
GROUP BY EMPNO
ORDER BY EMPNO


Try this one -
SELECT  EMPNO
    ,   [ROWCOUNT] = COUNT(1)
FROM dbo.Employee
WHERE StartDate BETWEEN DATEADD(MONTH, -3, GETDATE()) AND GETDATE()
GROUP BY EMPNO
ORDER BY EMPNO --[ROWCOUNT] DESC


Try this
SELECT
EMPNO,COUNT(STARTDATE)
FROM EMPLOYEE
WHERE STARTDATE BETWEEN DATEADD(month, -3, GETDATE()) and GETDATE()
GROUP BY EMPNO
ORDER BY EMPNO


You've got a typo in the select: "Betwenn" - otherwise I agree with this solution.
