Hello,
I am trying to create a stored proc that will capture already harvested SQL Server Agent Job information. Some of the key data incldues SQL Server Agent Job start time, and duration in minutes. I am trying to create a stored proc that will add 1 minute to the SQL Server Agent Job Start Time for however many total minutes the job ran. So if a job ran for 120 minutes and the job start time was at 5:00PM. I would like 120 additional data points with 5:01, 5:02, 5:03, etc. as start times.
The reason I am trying to do this is to create a Tableau report that shows me a graph of all SQL Server Agent Jobs running at any given time at a server level, and enterprise level. Currently a job that runs for 2 hours and starts at 5pm will only show up once on my viz. Thus I am trying to create the additonal data points that will show the job running at each minute interval, for however long that job ran for.
I can easily generate the list of columns with the new date times like so:
Declare @CNT int
Declare @x int
Declare @z datetime
Create Table #DateSplit ( Cnt int, x int, z datetime )
Select
@x = [Duration_MIN],
@z = [Start_DTM]
From #TempTable
Set @CNT = 0;
while (@CNT < @x)
Begin
Insert Into #DateSplit Values (null, null, @z )
set @z = dateadd (minute,1 ,@z)
set @CNT = @CNT + 1
end
GO
Here is my Main Select Statement for reference:
SELECT Distinct
[STG_JobHistory].[History_ID] AS [History_ID],
[STG_JobHistory].[Job_NME] AS [Job_NME],
[STG_JobHistory].[Start_DTM] AS [Start_DTM],
[STG_JobHistory].[Duration_SEC] AS [Duration_SEC],
[STG_JobHistory].[Outcome_DSC] AS [Outcome_DSC],
[STG_JobHistory].[Run_ID] AS [Run_ID],
[STG_JobHistory].[Server_ID] AS [Server_ID],
[STG_JobHistory].[RunServer_ID] AS [RunServer_ID],
[STG_JobHistory].[LastModified_DTM] AS [LastModified_DTM],
[STG_Server].[Prod_TYP] AS [Prod_TYP],
[STG_Server].[Server_NME] AS [Server_NME],
[STG_Job].[Job_DSC] AS [Job_DSC],
[STG_Job].[JobCategory_DSC] AS [JobCategory_DSC],
[STG_Job].[JobOwner_NME] AS [JobOwner_NME],
Duration_SEC/60 AS [Duration_MIN]
–into #TempTable
FROM [dbo].[STG_JobHistory] [STG_JobHistory]
INNER JOIN [dbo].[STG_Server] [STG_Server] ON ([STG_JobHistory].[Server_ID] = [STG_Server].[Server_ID])
INNER JOIN [dbo].[STG_Job] [STG_Job] ON ([STG_JobHistory].[Server_ID] = [STG_Job].[Server_ID] and [STG_JobHistory].[Job_NME] = [STG_Job].[Job_NME])
INNER JOIN [dbo].[STG_JobSchedule] [STG_JobSchedule] ON ([STG_JobHistory].[Server_ID] = [STG_JobSchedule].[Server_ID] and [STG_JobHistory].[Job_NME] = [STG_JobSchedule].[Job_NME])
and [STG_JobHistory].[Start_DTM] > cast (@StartDate as varchar )
However, I am running into problems linking this back into my original data set tied to the same Agent Job ID.
Thank you in advance!