// Critical05:
// Test and set results for Split Services required and not found.
// Test:
SELECT SID, DC2.ServiceID
FROM (SELECT DC1.*, tServiceType.ID, tServiceType.SplitService
FROM (SELECT tServiceID.ID AS SID, DC.ServiceID, 
 (CASE WHEN DC.ServiceType IS NULL THEN tServiceType.Code ELSE DC.ServiceType END) AS "Code"
FROM ((SELECT tDataCollection.ServiceID, tDataCollection.ServiceType
 FROM tDataCollection
 WHERE tDataCollection.BatchID = 11111
 GROUP BY tDataCollection.ServiceID, tDataCollection.ServiceType) AS DC
 LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1
 LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code
 WHERE tServiceType.SplitService = 1) AS DC2
LEFT JOIN tServiceSplit ON DC2.SID = tServiceSplit.ServiceID
WHERE tServiceSplit.ServiceID IS NULL

// Update:
UPDATE tDataCollection
SET tDataCollection.StatusID = 5
FROM (SELECT DC2.ServiceID
FROM (SELECT DC1.*, tServiceType.ID, tServiceType.SplitService
FROM (SELECT tServiceID.ID AS SID, DC.ServiceID, 
 (CASE WHEN DC.ServiceType IS NULL THEN tServiceType.Code ELSE DC.ServiceType END) AS "Code"
FROM ((SELECT tDataCollection.ServiceID, tDataCollection.ServiceType
 FROM tDataCollection
 WHERE tDataCollection.BatchID = 11111
 AND tDataCollection.StatusID IS NULL
 GROUP BY tDataCollection.ServiceID, tDataCollection.ServiceType) AS DC
 LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1
 LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code
 WHERE tServiceType.SplitService = 1) AS DC2
LEFT JOIN tServiceSplit ON DC2.SID = tServiceSplit.ServiceID
WHERE tServiceSplit.ServiceID IS NULL) AS DC2
WHERE tDataCollection.BatchID = 11111
AND tDataCollection.ServiceID = DC2.ServiceID


// Critical09:
// 9: Test for entries in tServiceSplit without tServiceType.SplitService checked.
SELECT SID, DC2.ServiceID
FROM (SELECT DC1.*, tServiceType.ID, tServiceType.SplitService
FROM (SELECT tServiceID.ID AS SID, DC.ServiceID, (CASE
 WHEN DC.ServiceType IS NULL THEN tServiceType.Code ELSE DC.ServiceType END) AS "Code"
FROM ((SELECT tDataCollection.ServiceID, tDataCollection.ServiceType
 FROM tDataCollection
 WHERE tDataCollection.BatchID = 11111
 GROUP BY tDataCollection.ServiceID, tDataCollection.ServiceType) AS DC
 LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1
 LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code
 WHERE tServiceType.SplitService <> 1) AS DC2
LEFT JOIN tServiceSplit ON DC2.SID = tServiceSplit.ServiceID
WHERE tServiceSplit.ServiceID IS NOT NULL
AND tServiceSplit.Active = 1


// Update:
UPDATE tDataCollection
SET tDataCollection.StatusID = 9
FROM (SELECT DC2.ServiceID
FROM (SELECT DC1.*, tServiceType.ID, tServiceType.SplitService
FROM (SELECT tServiceID.ID AS SID, DC.ServiceID, (CASE
 WHEN DC.ServiceType IS NULL THEN tServiceType.Code ELSE DC.ServiceType END) AS "Code"
FROM ((SELECT tDataCollection.ServiceID, tDataCollection.ServiceType
 FROM tDataCollection
 WHERE tDataCollection.BatchID = 11111
 AND tDataCollection.StatusID IS NULL
 GROUP BY tDataCollection.ServiceID, tDataCollection.ServiceType) AS DC
 LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1
 LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code
 WHERE tServiceType.SplitService <> 1) AS DC2
LEFT JOIN tServiceSplit ON DC2.SID = tServiceSplit.ServiceID
WHERE tServiceSplit.ServiceID IS NOT NULL
AND tServiceSplit.Active = 1) AS DC3
WHERE tDataCollection.BatchID = 11111
AND tDataCollection.ServiceID = DC3.ServiceID