# Tracking jobs over time

• Hello

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.

Thanks.

• Sample data, which I should have provided:

DECLARE @t TABLE ( JobType NVARCHAR(50), JobStart DATETIME, JobFinish DATETIME )
INSERT INTO @t
(JobType,JobStart, JobFinish)
VALUES
('Type1', '20183103', '20180421')
,('Type2', '20180402', '20180420')
,('Type3', '20180406', NULL)
,('Type4', '20180625', NULL)

• take in your desired point in time to compare to as a parameter. Defaults to current date and time, which you already know how to do to get current workload. To get it at some point in the past, just change your parameter, it will look for any jobs where JobStart <= @pointInTime and JobFinish >= @pointInTime

then you can repeat that process as many times as you wish to compare weeks, jumping to whatever datetime you want to use, stuff it all in a temp table/table variable for formatting, select everything from that at the end for your report.

-------------------------------------------------------------------------------------------------------------------------------------

• I might break this into two parts to get started. 1) jobs that are finished, 2) jobs that are not.

You could create a couple CTEs that would solve each of these issues and then put them together.

• jonathan.crawford - Monday, July 9, 2018 7:40 AM

take in your desired point in time to compare to as a parameter. Defaults to current date and time, which you already know how to do to get current workload. To get it at some point in the past, just change your parameter, it will look for any jobs where JobStart <= @pointInTime and JobFinish >= @pointInTime

then you can repeat that process as many times as you wish to compare weeks, jumping to whatever datetime you want to use, stuff it all in a temp table/table variable for formatting, select everything from that at the end for your report.

Good idea - thanks.

• Steve Jones - SSC Editor - Tuesday, July 10, 2018 9:17 AM

I might break this into two parts to get started. 1) jobs that are finished, 2) jobs that are not.

You could create a couple CTEs that would solve each of these issues and then put them together.

Thanks for the reply. I'm investigating the CTE way.

Viewing 6 posts - 1 through 5 (of 5 total)