A
Amin
Hi, so I wanted help on how to approach this query. I am completely willing
to re-do how I have inputted this information into my table, if someone has a
better suggestion.
So I have a team that gets work constantly throughout the day. They take
about 5 minutes to do each workload, and then they complete it. With the help
of this forum, I have created queries using the partition function that show
how much work is given every 15 minutes between two different dates, like in
the example below (note that each interval, represents 15 minutes)
25 26 27 28 <----These are the intervals
4 3 15 12 <---- These are the
workloads received
in that
interval
What I would like to do is augment this information with manhours available
in every 15 minute period. So I have ten people on my team, so what I would
like is for the data to look like this:
25 26 27 28 <----These are the intervals
10 9 8 9 <---- These are the workers
working
in that
interval
Each of these workers has a set lunch time and a set two break times, but
obviously they don't all lunch at the same time together and break at the
same time together. They all also have vacations, half-days, overtime,
etcetera.
What I have done is created a table with their normal hours consisting of a
Start Work Time, End Work Time, Lunch Start Time, Lunch End Time, Break1
Start Time, Break1 End Time, Break2 Start Time, Break2 End Time
I have also created a "Exceptions table" for the days people have taken
vacation, or reduced hours, or whatever. In this table, I have added the date
that this exception occured and the hours that were worked. So if they took a
full day of vacation, all of the fields would be blank except the date. Or if
they started working at noon instead of 8 AM, I inputted noon as the start
time, and null for the Break 1 times which they missed.
So do you think I've gone about this the right way? What should my next step
be? I know this is a tough one, but any thoughts or suggestions would be
greatly appreciated.
Thanks so much,
Amin
to re-do how I have inputted this information into my table, if someone has a
better suggestion.
So I have a team that gets work constantly throughout the day. They take
about 5 minutes to do each workload, and then they complete it. With the help
of this forum, I have created queries using the partition function that show
how much work is given every 15 minutes between two different dates, like in
the example below (note that each interval, represents 15 minutes)
25 26 27 28 <----These are the intervals
4 3 15 12 <---- These are the
workloads received
in that
interval
What I would like to do is augment this information with manhours available
in every 15 minute period. So I have ten people on my team, so what I would
like is for the data to look like this:
25 26 27 28 <----These are the intervals
10 9 8 9 <---- These are the workers
working
in that
interval
Each of these workers has a set lunch time and a set two break times, but
obviously they don't all lunch at the same time together and break at the
same time together. They all also have vacations, half-days, overtime,
etcetera.
What I have done is created a table with their normal hours consisting of a
Start Work Time, End Work Time, Lunch Start Time, Lunch End Time, Break1
Start Time, Break1 End Time, Break2 Start Time, Break2 End Time
I have also created a "Exceptions table" for the days people have taken
vacation, or reduced hours, or whatever. In this table, I have added the date
that this exception occured and the hours that were worked. So if they took a
full day of vacation, all of the fields would be blank except the date. Or if
they started working at noon instead of 8 AM, I inputted noon as the start
time, and null for the Break 1 times which they missed.
So do you think I've gone about this the right way? What should my next step
be? I know this is a tough one, but any thoughts or suggestions would be
greatly appreciated.
Thanks so much,
Amin