Tracking jobs over time

Question

Tracking jobs over time

faulknerwilliam2

SSCrazy

Points: 2299
More actions
July 9, 2018 at 2:12 am

#354442

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:
Workload:
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.

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

You must be logged in to reply to this topic. Login to reply

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 1

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)

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 2

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.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

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.

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 4

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.

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 5

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.