Row Number() and Over()

Transact-SQL
1

I have a table that contains records for EmployeeId, Project, Task and EntryDate. I'm trying to get a row number to be the same for every instance that the EmployeeId and month(date) are the same.

My sql statement looks like this:

ROW_NUMBER() OVER (EMPLOYEEID, month(EntryDate))

I also tried this:
ROW_NUMBER() OVER (PARTITION BY A.EMPLOYID ORDER BY A.EMPLOYID)

--figured I would remove the date to see if it would work correctly by employeeid

The problem is that I'm just getitng a new row number on every line even though all the records in the table have an Entry Date in May (i.e. 5/1/2015, 5/2/2015, 5/3/2015), etc.

What I'm getting is this:
Row Number, EmployeeId, Project, Task, Entry Date
1, EMP001, PROJA, TASK1, 5/1/2015
2, EMP001, PROJA, TASK2, 5/1/2015
3, EMP001, PROJA, TASK2, 5/2/2015
4, EMP002, PROJA, TASK1, 5/2/2015

What I want is this:

Row Number, EmployeeId, Project, Task, Entry Date
1, EMP001, PROJA, TASK1, 5/1/2015
1, EMP001, PROJA, TASK2, 5/1/2015
1, EMP001, PROJA, TASK2, 5/2/2015
2, EMP002, PROJA, TASK1, 5/2/2015

Any ideas on what I'm missing? I've tried Partition as well but still getting the same results.

Any help is greatly appreciated!

2

Try DENSE_RANK function instead of ROW_NUMBER function.

3

ROW_NUMBER() OVER (partition by EMPLOYEEID, year(EntryDate), month(EntryDate))

4

I have a table that contains records [sic: rows are not anything like records] for employee_id, project_name, task_name and entry_date.<<

Where is the DDL for this? Now we have to start guessing and doing for you what you should have done for us. Keys, constraints, data types, etc. You also failed to use ISO-8601 rules for dates, so I had to guess at that, too. And the table has no name!

Here is my guess:

CREATE TABLE Project_Assignments
(employee_id CHAR(6) NOT NULL,
project_name CHAR(5) NOT NULL,
task_name CHAR(5) NOT NULL,
PRIMARY KEY (employee_id, project_name, task_name),
entry_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);

INSERT INTO Project_Assignments
VALUES
('EMP001', 'PROJA', 'TASK1', '2015-05-01'),
('EMP001', 'PROJA', 'TASK2', '2015-05-01'),
('EMP001', 'PROJA', 'TASK2', '2015-05-02'),
('EMP002', 'PROJA', 'TASK1', '2015-05-02');

I'm trying to get a row number to be the same for every instance that the employee_id and MONTH(entry_date) are the same. <<

No. You do not understand what ROW_NUMBER() does. It is a function call and its results are unique within each window frame.

The problem is that I'm just getting a new row number on every line even though all the records [sic] in the table have an entry_date in May (i.e. '2015-05-01', '2015-05-02', '2015-05-03'), etc. <<

Gee, now we know how to repair your dates from the narrative. But we wanted to get this spec from valid DDL and not a narrative. This will give you the desired results:

SELECT DENSE_RANK() OVER (ORDER BY employee_id)
AS something_group,
employee_id, project_name, task_name, entry_date
FROM Project_Assignments;