asked
Compare temp tables
Hi , I need to compare a log table against a look up table to find if the job is complete. For example, The job may have 6 steps and the start and end steps of the job need to be completed to say the job is complete. I have a look up table which holds the job steps. Please find below the script. CREATE TABLE [dbo].[JobLog]( [JobName] [varchar](20) NULL, [StepName] [varchar](20) NULL, [Status] [varchar](20) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[LkpJob]( [JobName] [varchar](20) NULL, [StepName] [varchar](20) NULL ) ON [PRIMARY] INSERT [dbo].[JobLog] ([JobName], [StepName], [Status]) VALUES (N'Process Job', N'StartStep', N'Success') INSERT [dbo].[JobLog] ([JobName], [StepName], [Status]) VALUES (N'Process Job', N'Step1', N'Success') INSERT [dbo].[JobLog] ([JobName], [StepName], [Status]) VALUES (N'Process Job', N'Step3', N'Success') INSERT [dbo].[JobLog] ([JobName], [StepName], [Status]) VALUES (N'Process Job', N'Step4', N'Success') INSERT [dbo].[JobLog] ([JobName], [StepName], [Status]) VALUES (N'Process Job', N'EndStep', N'Success') INSERT [dbo].[LkpJob] ([JobName], [StepName]) VALUES (N'Process Job', N'StartStep') INSERT [dbo].[LkpJob] ([JobName], [StepName]) VALUES (N'Process Job', N'Step1') INSERT [dbo].[LkpJob] ([JobName], [StepName]) VALUES (N'Process Job', N'Step2') INSERT [dbo].[LkpJob] ([JobName], [StepName]) VALUES (N'Process Job', N'Step3') INSERT [dbo].[LkpJob] ([JobName], [StepName]) VALUES (N'Process Job', N'Step4') INSERT [dbo].[LkpJob] ([JobName], [StepName]) VALUES (N'Process Job', N'EndStep')
with CTE as ( select jobname, count(*) as nr_of_steps from lkpJob group by jobname) , CTE2 as (select 'succes' as status, jobname, count(*) as nr_steps from joblog where status = 'Success' group by jobname intersect select 'succes', jobname, nr_of_steps from CTE union select 'failed', jobname, count(*) from joblog where status = 'Success' group by jobname except select 'failed', jobname, nr_of_steps from CTE ) select jobname, status from CTE2
Thanks for your answer Wilfred. In some cases if the look up table doesnt have the number of steps correctly but has the start and end steps then how can we re write this based on start and end steps comparison ?
This query will give you all the jobs which have at least 2 succesfull steps ( assuming 'startstep' and 'endstep' is always logged and endstep is only 'Success' if all the steps are 'Success'): select jobname, count(*) from joblog where status = N'Success' group by jobname having count(*) > 1
18 People are following this question.
