4pm sql command backup

1, turn on Email report for all agents of IPAV, if they have any property.

update [findahome].[dbo].[AgentContactFrequency] set Frequency = 7 where AgentId in (
  SELECT Distinct(acf.AgentId)
  FROM [findahome].[dbo].[AgentContactFrequency] acf
  LEFT Join [findahome].[dbo].[Agent] a on a.idAgent = acf.AgentId
  LEFT Join [findahome].[dbo].[Property] p on p.idCreatedBy = a.idUser 
  LEFT Join [findahome].[dbo].[AgencyOffice] ao on ao.idOffice = a.idOffice
  LEFT JOIN [findahome].[dbo].[Agency] ay on ay.idAgency = ao.idAgency
  WHERE acf.Frequency = -1 AND ay.idAffiliation = 3
  )

 

2, Open the Email Report for IPAV Agents, that not in the AgencyContactFrequecy yet (Insert Multiply record from select query)

INSERT INTO [findahome].[dbo].[AgentContactFrequency]
  SELECT a.idAgent, '2000-01-01 00:00:00.000', 7, 1 FROM [findahome].[dbo].[Agent] a 
  LEFT Join [findahome].[dbo].[AgencyOffice] ao on ao.idOffice = a.idOffice  
  LEFT JOIN [findahome].[dbo].[Agency] ay on ay.idAgency = ao.idAgency  
  WHERE ay.idAffiliation = 3 AND a.idAgent not in (SELECT AgentId FROM [findahome].[dbo].[AgentContactFrequency]) 

3, Turn OFF the IPAV agents if no any Property 

update [findahome].[dbo].[AgentContactFrequency] set Frequency = -1 where AgentId not in (
  SELECT Distinct(acf.AgentId)
  FROM [findahome].[dbo].[AgentContactFrequency] acf  
  LEFT Join [findahome].[dbo].[Agent] a on a.idAgent = acf.AgentId  
  RIGHT Join [findahome].[dbo].[Property] p on p.idCreatedBy = a.idUser   
  LEFT Join [findahome].[dbo].[AgencyOffice] ao on ao.idOffice = a.idOffice  
  LEFT JOIN [findahome].[dbo].[Agency] ay on ay.idAgency = ao.idAgency  
  WHERE ay.idAffiliation = 3) AND AgentId in (SELECT Distinct(acf.AgentId)
  FROM [findahome].[dbo].[AgentContactFrequency] acf  
  LEFT Join [findahome].[dbo].[Agent] a on a.idAgent = acf.AgentId  
  LEFT Join [findahome].[dbo].[Property] p on p.idCreatedBy = a.idUser   
  LEFT Join [findahome].[dbo].[AgencyOffice] ao on ao.idOffice = a.idOffice  
  LEFT JOIN [findahome].[dbo].[Agency] ay on ay.idAgency = ao.idAgency  
  WHERE ay.idAffiliation = 3)