I have a work database that lists:
Type of job
Date job started
Date job finished
I have been asked if I can produce a report that can be run each Monday (or whenever) that:
a) shows the current workload and
b) shows what the workload was the previous week (and each previous week of the current financial year - 1/4/18 - 31/3/19).
Its date parameters would be:
week 1: 1/4/18 - 7/4/18
week 2: 8/4/18 - 15/4/18 etc
so I would be looking at live workload totals as at 1/4/18, 8/4/18 etc
If there is no date in the 'date job finished' field then a job is considered live.
So for example if we look at these 4 jobs:
job 1 started 31/3/18 finished 21/4/18
job 2 started 2/4/18 finished 20/4/18
job3 started 6/4/18 unfinished
and job 4 start on 10/4/18 unfinished
And ran a report on 22/4/18 I would need its output to be, sowing what the workload was on these weeks:
Week 1(1/4/18) Week 2 (8/4/18) Week 3 Week 4
3 3 4 2
So in terms of current workload - that's easy and just a count of jobs where the 'date job finished' field is null.
However I'm struggling to work out how to calculate the workload over time - basically I need a query to work out how to get an accurate count of when the date job finished field was null in a particular week.
Any help / assistance would be massively appreciated.