July 9, 2018 at 2:12 am
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.
July 9, 2018 at 4:11 am
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)
July 9, 2018 at 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.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
July 10, 2018 at 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.
July 12, 2018 at 8:58 am
jonathan.crawford - Monday, July 9, 2018 7:40 AMtake 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 >= @pointInTimethen 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.
July 12, 2018 at 8:59 am
Steve Jones - SSC Editor - Tuesday, July 10, 2018 9:17 AMI 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)
You must be logged in to reply to this topic. Login to reply