How to calculate number of weeks worked by an employee for a fiscal year in sql server

Question

How to calculate number of weeks worked by an employee for a fiscal year in sql server

Maddi, Shyam 0

How to determine the total weeks an employee has worked during a fiscal year using SQL Server. My start date is 12-28-2023 and till today, I would like to calculate the no.of weeks worked by an employee during that time.

Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2024-02-02T22:04:40.1633333+00:00

How do you track whether an employee has actually worked, or been on leave? Do you have a table for this? In that case, we need to see that table in order to help you.
Maddi, Shyam 0 Reputation points

2024-02-02T22:18:52.29+00:00

Hello Erland,

Yes, we have a Worker Table, with HireDate and TermDate, I need to get Weeks worked by employees for this FY. If we select 02/02/2024, I have to get WorkerWeeks=5 (FY StartDate=12/30/2023, so from 12/30/2023 to 02/02/2024 is 5 weeks)
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2024-02-02T22:23:58.4666667+00:00

If you don't care about leaves, Michael Taylor's answer should do.
Maddi, Shyam 0 Reputation points

2024-02-02T22:34:02.78+00:00

I dont think that works for me, because Worker Weeks have to reset every FiscalYear.
for FY 2024 the weeks should be calculated from 12/30/2023 to 12/28/2024.
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2024-02-02T22:40:07.57+00:00

Yes, you would have to set the variables according to the current situation. But since Michael has no idea how you know when then fiscal year started, he could not do better.

Keep in mind that when you ask a question, we can only work from the information you share. We don't know your database or your business rules. If your questions is "how do i know when the fiscal year start", our answer can't but be "We don't know".
LiHongMSFT-4306 31,566 Reputation points

2024-02-05T03:32:24.3566667+00:00

Hi @Maddi, Shyam

My start date is 12-28-2023 and till today, I would like to calculate the no.of weeks worked by an employee during that time.

Have you check if the Datediff function worked here?

1 answer

Your answer

Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2024-02-02T22:04:40.1633333+00:00

How do you track whether an employee has actually worked, or been on leave? Do you have a table for this? In that case, we need to see that table in order to help you.
Maddi, Shyam 0 Reputation points

2024-02-02T22:18:52.29+00:00

Hello Erland,

Yes, we have a Worker Table, with HireDate and TermDate, I need to get Weeks worked by employees for this FY. If we select 02/02/2024, I have to get WorkerWeeks=5 (FY StartDate=12/30/2023, so from 12/30/2023 to 02/02/2024 is 5 weeks)
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2024-02-02T22:23:58.4666667+00:00

If you don't care about leaves, Michael Taylor's answer should do.
Maddi, Shyam 0 Reputation points

2024-02-02T22:34:02.78+00:00

I dont think that works for me, because Worker Weeks have to reset every FiscalYear.
for FY 2024 the weeks should be calculated from 12/30/2023 to 12/28/2024.
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2024-02-02T22:40:07.57+00:00

Yes, you would have to set the variables according to the current situation. But since Michael has no idea how you know when then fiscal year started, he could not do better.

Keep in mind that when you ask a question, we can only work from the information you share. We don't know your database or your business rules. If your questions is "how do i know when the fiscal year start", our answer can't but be "We don't know".
LiHongMSFT-4306 31,566 Reputation points

2024-02-05T03:32:24.3566667+00:00

Hi @Maddi, Shyam

My start date is 12-28-2023 and till today, I would like to calculate the no.of weeks worked by an employee during that time.

Have you check if the Datediff function worked here?

Answer 1

Michael Taylor 60,326

Have you tried using DATEDIFF?

DECLARE @start DATETIME, @end DATETIME
SET @start = '12/28/2023'
SET @end = '12/27/2024'

SELECT DATEDIFF(week, @start, @end)

A caveat here is that weeks worked isn't well defined. This is the # of weeks between 2 dates but might not line up with what you consider to be work weeks. For example it doesn't take into account holidays or weekends. If you need that then the calculation will get a lot more complicated as weekends need to be removed and holidays are not a fixed list.

Share via

How to calculate number of weeks worked by an employee for a fiscal year in sql server

1 answer

Your answer