CODEWITHSUNDEEP
sqlsql-server
user2813860
user2813860

Reputation: 13

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,

Upvotes: 1

Views: 1153

Answers (4)

Chamal
Chamal

Reputation: 1449

Try this

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

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460018

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.

Upvotes: 1

Devart
Devart

Reputation: 121902

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

Upvotes: 1

CodingIntrigue
CodingIntrigue

Reputation: 78525

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

Upvotes: 1

Related Questions