VERIFIED SOLUTION i

How to compose an SQL query to delete a job in OfficeMail

Provided is the SQL query used to "delete job prior to 1st April 2016" and "mark job after 1st April 2016 to Completed":

use PIOfficeMail
go

disable Trigger Trigger_Job_D ON piofficemail.dbo.job;
begin tran
delete from piofficemail.dbo.pdfKeys where KeyIndex in (select KeyIndex from piofficemail.dbo.job where ReceivedDate<'2016/04/01')
delete from piofficemail.dbo.job where ReceivedDate<'2016/04/01';
delete from piofficemail.dbo.jobbundle where SubmittedToVIPDate <'2016/04/01'and 
(not exists(select j.jobbundleid from PiOfficemail.dbo.job j where j.JobBundleID= PiOfficemail.dbo.jobbundle.jobbundleid ));
delete from piofficemail.dbo.jobprintlog where ReceivedDate<'2016/04/01';
commit tran;

enable Trigger Trigger_Job_D ON piofficemail.dbo.job;

go

disable Trigger Trigger_Job_U ON piofficemail.dbo.job;
declare @dd datetime

set @dd = getdate()
begin tran
update piofficemail.dbo.job set JobStatusID=4, CompletedDate=@dd where ReceivedDate>'2016/04/01';
update piofficemail.dbo.jobprintlog set JobStatus='Completed', CompletedDate=@dd, JobStatusID=4  where ReceivedDate>'2016/04/01';

commit tran;

enable Trigger Trigger_Job_U ON piofficemail.dbo.job;

go
Environment Details

 
UPDATED:  July 3, 2017