I have a custom object Referral__c which has a lookup to Account and Oppty. Usecase is whenever a new oppty is created and Account on that oppty has a Referral, attach oppty to the Referral as well. My below code works perfectly fine, but would like to know how can I get Soql outside for loop.

 public void AddOpptytoReferral(List<Opportunity> newOpps)
      {
        List<Referral__c> RPtoupdate=new list<Referral__c>();  
        for(Opportunity Opp: newOpps)
       {  
        if(Opp.RecordTypeid=='012j0000000z1iCAAQ' || Opp.RecordTypeid=='012j0000000z1lBAAQ')
        { 
          List<Referral__c> RP=new list<Referral__c>([select id,Opportunity__c from Referral__c where Referral_Account__c= :Opp.AccountId and Expiration_date__c>= today and Opportunity__c=null and Referral_Type__c='Customer Referral' order by createdDate asc limit 1]);
            
            if(RP.size() > 0)
            {
             RP[0].Opportunity__c=Opp.Id;  
             RPtoupdate.add(RP[0]);
            }
         }
       }
          if(RPtoupdate.size()>0){
              update RPtoupdate;}
       }

 I created a batch apex and schedulable apex and scheduled apex to send emails to all contacts on an Account associated with an open case of a specific sub-category. The batch apex works but there are some batch failures, I am unable to know status of each record in a batch as to whom email was sent and not and the reason behind batch failure. On an average there are about 833 records processed in 85 batches out of which 52 batches got failed. Not sure how many records in each batch got failed and why. How can I get status of 833 records which ones got processed and which ones got failed? Below is my batch class and schedulable class.
Batch Apex:
global class CaseNotifications implements Database.Batchable<sObject>,Database.Stateful {
    global string contactIds;
    global integer count = 0;

      global Database.QueryLocator start(Database.BatchableContext BC)
      {
          string subcategory1='Hardware';  
          string subcategory2='Software';
          return Database.getQueryLocator('select id from contact where accountId in (select accountId from case where Isclosed= false and (sub_category__c=\''+subcategory1+'\' or sub_category__c=\''+subcategory2+'\'))' );      
      }
    
    global void execute(Database.BatchableContext BC, List<sObject> scopeList) {
     List<Messaging.SingleEmailMessage> emails = new list<Messaging.SingleEmailMessage>();
        List<Contact> toAddresses=new List<Contact> ([select email from contact where id in :scopelist]);
        system.debug('ContactIds size'+toaddresses.size());
        if(toAddresses.size()>0){
         for(Contact c:toAddresses){
             if(c.email!=null){
               count++;

         Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();
         Message.setTargetObjectId(c.id);
         Message.setTemplateId('00Xj00000011tbR');
         Message.setOrgWideEmailAddressId('0D2j00000000BAM');    
         emails.add(Message); 
             }}
        Messaging.sendEmail(emails);
        }
                 
         }
    
    global void finish(Database.BatchableContext BC){
        AsyncApexJob aaj = [Select Id, Status, NumberOfErrors, JobItemsProcessed, MethodName, TotalJobItems, CreatedBy.Email from AsyncApexJob where Id =:BC.getJobId()];
        
        // Send an email to the Apex job's submitter notifying of job completion.
        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
        String[] toAddresses = new String[] {aaj.CreatedBy.Email};
        mail.setToAddresses(toAddresses);
        mail.setSubject('JOB Salesforce Send Notification Batch: ' + aaj.Status);
        String bodyText='Batches Processed ' + aaj.TotalJobItems + 'Total number of records processed ' + count+ 'with '+ aaj.NumberOfErrors + ' batch failures.\n';
        mail.setPlainTextBody(bodyText);
        Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
    }

}

Schedulable Apex:
global class BackupCaseNotifications implements schedulable {
    global void execute(SchedulableContext ctx) {
        CaseNotifications b=new CaseNotifications();
        Database.executeBatch(b,10);
    }

}

 I created a batch apex and schedulable apex and scheduled apex to send emails to all contacts on an Account associated with an open case of a specific sub-category. The batch apex works but there are some batch failures, I am unable to know status of each record in a batch as to whom email was sent and not and the reason behind batch failure. On an average there are about 833 records processed in 85 batches out of which 52 batches got failed. Not sure how many records in each batch got failed and why. How can I get status of 833 records which ones got processed and which ones got failed? Below is my batch class and schedulable class.
Batch Apex:
global class CaseNotifications implements Database.Batchable<sObject>,Database.Stateful {
    global string contactIds;
    global integer count = 0;

      global Database.QueryLocator start(Database.BatchableContext BC)
      {
          string subcategory1='Hardware';  
          string subcategory2='Software';
          return Database.getQueryLocator('select id from contact where accountId in (select accountId from case where Isclosed= false and (sub_category__c=\''+subcategory1+'\' or sub_category__c=\''+subcategory2+'\'))' );      
      }
    
    global void execute(Database.BatchableContext BC, List<sObject> scopeList) {
     List<Messaging.SingleEmailMessage> emails = new list<Messaging.SingleEmailMessage>();
        List<Contact> toAddresses=new List<Contact> ([select email from contact where id in :scopelist]);
        system.debug('ContactIds size'+toaddresses.size());
        if(toAddresses.size()>0){
         for(Contact c:toAddresses){
             if(c.email!=null){
               count++;

         Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();
         Message.setTargetObjectId(c.id);
         Message.setTemplateId('00Xj00000011tbR');
         Message.setOrgWideEmailAddressId('0D2j00000000BAM');    
         emails.add(Message); 
             }}
        Messaging.sendEmail(emails);
        }
                 
         }
    
    global void finish(Database.BatchableContext BC){
        AsyncApexJob aaj = [Select Id, Status, NumberOfErrors, JobItemsProcessed, MethodName, TotalJobItems, CreatedBy.Email from AsyncApexJob where Id =:BC.getJobId()];
        
        // Send an email to the Apex job's submitter notifying of job completion.
        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
        String[] toAddresses = new String[] {aaj.CreatedBy.Email};
        mail.setToAddresses(toAddresses);
        mail.setSubject('JOB Salesforce Send Notification Batch: ' + aaj.Status);
        String bodyText='Batches Processed ' + aaj.TotalJobItems + 'Total number of records processed ' + count+ 'with '+ aaj.NumberOfErrors + ' batch failures.\n';
        mail.setPlainTextBody(bodyText);
        Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
    }

}

Schedulable Apex:
global class BackupCaseNotifications implements schedulable {
    global void execute(SchedulableContext ctx) {
        CaseNotifications b=new CaseNotifications();
        Database.executeBatch(b,10);
    }

}