I have a job 1111 with three steps : 11111, 11112, 11113. Steps 11111 and 11112 have a specific delivery method. Step 11113 does not have a specific delivery method so it will use the job default delivery. I have tried to code it my self but the code is a mess. Can someone help me with the tsql code to get my expected results.
drop table job_step
drop table delivery_information
create table job_step (job_cmid int, job_step_cmid int)
create table delivery_information (cmid int, delivery varchar(250))
insert into job_step (job_cmid, job_step_cmid)
values
(1111, 11111),
(1111, 11112),
(1111, 11113)
insert into delivery_information (cmid, delivery)
values
(1111, 'save to f:\aaa'), -- default for job 1111 if there is not a jobstep specific override
(11111, 'send to peggy'),
(11112, 'hold for john'),
(2222, 'send letter'),
(22221, 'do nothing')
expected results
job_step delivery
1 send to peggy
2 hold for john
3 save to f:\aaa