Create series of counting for each employee by date-CodePudding

I am trying to list out a count of how many times an employeeid shows up for each date separated by lines and in the 0000 format. Each time the specific employeeid occurs on a specific date, the count goes up. So EmployeeId 143 happens twice on 2023-01-18, so the first row is 0001, second is 0002

SELECT 
    FORMAT(COUNT(e.EmployeeId), '0000') AS [Count] 
    , e.EmployeeId
    , c.CheckDate
FROM dbo.Check c
JOIN dbo.Employees e
    ON e.EmployeeId = c.CreatedBy
GROUP BY c.CheckDate, e.EmployeeId
ORDER BY c.CheckDate DESC;

What I'm currently getting:

COUNT	EmployeeId	CheckDate
0002	143	2023-01-18 00:00:00.000
0002	143	2023-01-17 00:00:00.000
0002	427	2023-01-17 00:00:00.000
0007	607	2023-01-17 00:00:00.000

What I am wanting is:

COUNT	EmployeeId	CheckDate
0001	143	2023-01-18 00:00:00.000
0002	143	2023-01-18 00:00:00.000
0001	143	2023-01-17 00:00:00.000
0002	143	2023-01-17 00:00:00.000
0001	427	2023-01-17 00:00:00.000
0002	427	2023-01-17 00:00:00.000

etc.

CodePudding user response：

My take of your issue here is that you are aggregating while you need a window function really.

To ensure unique values for your couples <CheckDate, EmployeeId> duplicates, you can try using the ROW_NUMBER window function.

SELECT FORMAT(ROW_NUMBER() OVER(
                  PARTITION BY c.CheckDate, e.EmployeeId
                  ORDER     BY e.EmployeeId
             ), '0000') AS [Count]
     , e.EmployeeId
     , c.CheckDate
FROM       dbo.Check c
INNER JOIN dbo.Employees e
        ON e.EmployeeId = c.CreatedBy
ORDER     BY c.CheckDate DESC;