I'm working on a dashboard to track project progress. The data source I have only provides snapshot information (how many total hours were completed for a project,) which we could pull weekly--or daily if needed. I'd like to convert this to transnational records showing how many hours were worked on a project in a given week. I'v been playing with different ways to loop through the dataset, but I feel like I'm missing something obvious. Could any of you experts help?
My source data looks like:
REPDATE
PID
PNAME
ESTHOURS
COMPHRS-BAL
REMHOURS-BAL
5/6/2019
10
Claims Dash
40
5
35
5/6/2019
20
Buy Model
20
0
20
5/6/2019
30
Loss Runner
20
2
18
5/6/2019
40
Premium Bdx
50
3
47
5/13/2019
10
Claims Dash
40
9
31
5/13/2019
20
Buy Model
20
2
18
5/13/2019
25
Sell Model
20
5
15
5/13/2019
30
Loss Runner
20
4
16
5/13/2019
40
Premium Bdx
50
15
35
5/13/2019
50
Excel Proj
10
10
0
And my target table looks something like:
REPDATE
PID
PNAME
COMPHRS-PERIOD
5/6/2019
10
Claims Dash
5
5/6/2019
20
Buy Model
0
5/6/2019
30
Loss Runner
2
5/6/2019
40
Premium Bdx
3
5/13/2019
10
Claims Dash
4
5/13/2019
20
Buy Model
2
5/13/2019
25
Sell Model
5
5/13/2019
30
Loss Runner
2
5/13/2019
40
Premium Bdx
12
5/13/2019
50
Excel Proj
10
Assuming we will be adding projects along the way.
