CREATE PROCEDURE dbo.spFindOutstandingJobs

AS
	UPDATE FileTransfers SET status = 'T' WHERE xfer_id IN
		/* Document has already been transcribed if it has a line in SorterDocs with the same tranid and jobnumber within 10 days of leech */
		(SELECT xfer_id FROM FileTransfers INNER JOIN SorterDocs ON
			FileTransfers.status='D' AND
			jobnumbers LIKE '%'+jobnum+'%' AND
			(DATEDIFF(dd,xfer_time,sort_time) < 10) AND
			SorterDocs.tran_id=CAST(ext AS int)
		);

	UPDATE FileTransfers SET status = 'T' WHERE xfer_id IN
		(SELECT xfer_id FROM FileTransfers INNER JOIN
			(SELECT * FROM stars.dbo.DictationEvents WHERE event_id IN 
				/* only use latest entry in DictationEvents for each machineID + jobnum combination, but only consider lines that match in the FileTransfers table */
				(SELECT MAX(event_id) FROM stars.dbo.DictationEvents INNER JOIN FileTransfers ON
					FileTransfers.status='D' AND
					job_num = jobnum AND
					SUBSTRING(filename,1,1) = CAST(machine_id AS char)
				GROUP BY CAST(machine_id AS varchar) + CAST(job_num AS varchar) )
			) AS A ON
				FileTransfers.status='D' AND
				job_num = jobnum AND
				SUBSTRING(filename,1,1) = CAST(machine_id AS char) AND
				(DATEDIFF(dd,xfer_time,tran_time) < 10) AND
				A.tran_id=CAST(ext AS int)
		WHERE job_status LIKE '%S%');

	UPDATE FileTransfers SET status = 'B' WHERE xfer_id IN
		(SELECT xfer_id FROM FileTransfers INNER JOIN
			(SELECT * FROM stars.dbo.DictationEvents WHERE event_id IN 
				/* only use latest entry in DictationEvents for each machineID + jobnum combination, but only consider lines that match in the FileTransfers table */
				(SELECT MAX(event_id) FROM stars.dbo.DictationEvents INNER JOIN FileTransfers ON
					FileTransfers.status='D' AND
					job_num = jobnum AND
					SUBSTRING(filename,1,1) = CAST(machine_id AS char)
				GROUP BY CAST(machine_id AS varchar) + CAST(job_num AS varchar) )
			) AS A ON
				FileTransfers.status='D' AND
				job_num = jobnum AND
				SUBSTRING(filename,1,1) = CAST(machine_id AS char) AND
				(DATEDIFF(dd,xfer_time,tran_time) < 10) AND
				A.tran_id<>CAST(ext AS int)
		);

	SELECT filename, ext, initials, convert(varchar(19),xfer_time,101) + " " + convert(varchar(19),xfer_time,108) AS xfer_time FROM FileTransfers WHERE status='D' ORDER BY xfer_time ASC;

GO