CODEWITHSUNDEEP
sqlsql-servert-sqlsql-order-by
Glock
Glock

Reputation: 13

Grouping Start Dates

Example of what I am trying to do:

I have 10 employees. They all started on different days throughout the year. Each get paid once a week. I want to query their first paycheck and call that week 1 for all employees. Then each subsequent paycheck will be 2...3...through 13. So basically I want to see what each of their first 13 weeks on the job looked like stacked against each other. I would expect my output to look something like this:

enter image description here

Upvotes: 0

Views: 38

Answers (1)

GMB
GMB

Reputation: 222682

You can use row_number():

select
    row_number() over(partition by EmployeeId order by PaycheckDate) week,
    EmployeeId,
    PaycheckDate,
    Amount
from mytable
order by week, EmployeeId

If you want just the first 13 weeks per employee, then:

select *
from (
    select
        row_number() over(partition by EmployeeId order by PaycheckDate) week,
        EmployeeId,
        PaycheckDate,
        Amount
    from mytable
) t
where week <= 13
order by week, EmployeeId

Upvotes: 1

Related Questions