Working hours based on Roster Pattern

[Anonymous]

Hello Experts .. 

I have a Employee Roster Pattern Table and a Date Table. 

Requirement is to calculate the total working hours for each employee based on the roster pattern and holidays/week ends.

Can you please help?

regards

DJ

Emplyee Roster Table
Person Number	ID Number	Base Hours	Description	Start Date	End Date	Sun	Mon	Tue	Wed	Thu	Fri	Sat
22990053	990053	D7	0-8-8-8-8	18/01/2019	17/01/2020	0	0	8	6	8	7	0
22990053	990053	H3	0-0-8-8-8	18/01/2020	31/12/4712	0	0	0	8	8	9	0
Date Table
Working Date	Week Day	Holiday	Roster Pattern
1/01/2020	4	1	6
2/01/2020	5		8
3/01/2020	6		7
4/01/2020	7	1	0
5/01/2020	1	1	0
6/01/2020	2		0
7/01/2020	3		8
8/01/2020	4		6
9/01/2020	5		8
10/01/2020	6		7
11/01/2020	7	1	0
12/01/2020	1	1	0
13/01/2020	2		0
14/01/2020	3		8
15/01/2020	4		6
16/01/2020	5		8
17/01/2020	6		7
18/01/2020	7	1	0	New Roster Pattrn Start
19/01/2020	1	1	0
20/01/2020	2		0
21/01/2020	3		8
22/01/2020	4		8
23/01/2020	5		9
24/01/2020	6		0
25/01/2020	7	1	0
26/01/2020	1	1	0
27/01/2020	2	1	0
28/01/2020	3		8
29/01/2020	4		8
30/01/2020	5		9
31/01/2020	6		0

[v-juanli-msft]

Hi @Anonymous 

In "Emplyee Roster Table", number "6,7,8" under "Tue,Wed,Thu" means how many hours emplyee works in these days, right?

In Date Table, "Roster Pattern" means how many hours an emplyee should work for this day,

The "Roster Pattern" is the same for every month as below, right?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Heu Maggie,

I need to look up teh Roster Pattern from the Roster table for a emoployee, and use it in Date table to calculate.

Roster pattern can chnage at teh midle of teh month as weel.

Thanks

DJ

[v-juanli-msft]

Hi @Anonymous 

I am still not clear.

For for example,  person 1's data in Roster tablem is as below:

person id	start date	end date	Sun	Mon	Tue	Wed	Thu	Fri	Sat
1	1/1/2020	1/15/2020	0	0	8	6	8	7	0

then i should check if the values from "Sun" to "Sat" with the "Roster Pattern" values in date table are consistent,

then use the values to calculate working hours, right?

Best Regards
Maggie