question

aRookieBIdev avatar image
aRookieBIdev 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')
sql-server-2012sql server
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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 ?
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.