SQL Case statement to return index of duplicate rows

Question

I'm running a query to return multiple rows of employee hours data from multiple tables. There is a valid condition of employees putting a day shift of 8 hours per day to multiple customers for billing purposes. I want to update the "Index" column with a unique index for any duplicate across 3 columns

eg.

Emp   Hrs   Date   Index
Fred  8     11/7   1
Fred  8     11/7   2
Fred  8     11/7   3
Fred  10    12/7   1
Fred  9     13/7   1

In the Select statement these is a Case statement which returns a value in the Index column for a duplicate but it doesn't give a unique value

case when count(*) over (partition by Emp, Hrs, Date) > 1
    then 1
        else 0
    end

can I include a group by condition in a case statement to achieve this?

Answer 1

Try this:

UPDATE x
SET x.Index = x.Index_Calc
FROM
(
    SELECT Index, ROW_NUMBER() OVER (PARTITION BY Emp, Hrs, Date ORDER BY (SELECT 1)) AS Index_Calc
    FROM yourTable
) x

I based my answer off this Stack Overflow question, but I believe it should work here as well.

Answer 2

You can achieve this with row_number() and partition by.

Partition will give you a row number that resets every time a new "group" of values is encountered.

create table billings (employee varchar(100), hours decimal(10,2), billed_on date)
go

insert into billings values
('Mike', 8, '2017-02-01'),
('Mike', 8, '2017-02-01'),
('Mike', 8, '2017-02-01'),
('Mike', 8, '2017-02-19'),
('John', 10, '2017-02-01'),
('John', 8, '2017-02-01');

select  employee, hours, billed_on,
        row_number() over (partition by employee, billed_on order by employee) as ix
from    billings
order by billed_on, employee, ix

Produces:

    employee  hours billed_on    ix
1   John      10,00 01.02.2017   1
2   John       8,00 01.02.2017   2
3   Mike       8,00 01.02.2017   1
4   Mike       8,00 01.02.2017   2
5   Mike       8,00 01.02.2017   3
6   Mike       8,00 19.02.2017   1