Interested in Jobs missing Schedules

Hi Peeps, 

This will be a running post.. 

I will keep updating the posts as I create the query.. 

so let's begin 

 Question-> We need to find the jobs which are missing the run in the schedule. 

 Now what do we need to get this into action. 

 a. Job is currently running flag 

b. Job Start Time 

c. Next Schedule Date/Time 

d. Job End Time 

e. If Job End Time> Next Schedule Date/Time || If Schedule Date/Time is passed and Job Start Date Time is not equal to Schedule Date/Time 

 a. Job is curently running flag

 This can be extracted from sysjobactivity and sysjobs 

 SELECT sj.Name, 

CASE

WHEN sja.start_execution_date IS NULL THEN 'Not running'

WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'

WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'

END AS 'RunStatus',sja.job_id,sja.start_execution_date,sja.stop_execution_date

FROM msdb.dbo.sysjobs sj

JOIN msdb.dbo.sysjobactivity sja

ON sj.job_id = sja.job_id

WHERE session_id = (

SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity);

b. Job Start Time can also be pulled from SubQuery1 

c. Next Schedule Date/Time 

This we can pull from sysjobschedules 

SubQuery2 

SELECT j.job_id,convert(NVARCHAR(24),(msdb.dbo.agent_datetime(s.next_run_date,s.next_run_time)),121) NextSchedule

   FROM msdb.dbo.sysjobs j

   INNER JOIN msdb.dbo.sysjobschedules s 

   ON s.job_id = j.job_id

d. Job End time from 

SubQuery1 

 to be continued..