How to build correct query notification?

Question

could someone please help me, I'm trying to set up alerts with the total amount of records for some tables that I want. In this example, I'm just trying to return COUNT as a result of one of the tables to say how many records don't have schedules for the customer, however with all these exceptions Creating a Query for Notification

,I couldn't think of a solution for my case.

SELECT COUNT(A.CODREF)QTDEAGENDSEMAGENDA FROM REGISTROS A INNER JOIN ATENDENTES U ON U.CODUSUARIO = A.CODUSUARIO WHERE A.CODUSUARIO = 11 AND A.STATUS IS NULL AND A.CODREF NOT IN ( SELECT CODREF FROM RETORNOS WHERE CODDIALOGO IS NULL AND AGEND_INTERNO IS NULL ) AND DATEDIFF(DAY, A.INICIO, GETDATE())> 11

All the queries I'm going to assemble will look like this in the example. I had thought of creating a view.

Calling my view:

SELECT QTDEAGENDSEMAGENDA FROM ALERTS

then the query would be simple and it would work, but I saw that it is also on the list not to be used.

This is my code and does not work with this query that I set up or with the View

 public class NotificationHub : Hub
{
    string qtdeAgendSemAgenda = string.Empty;

    [HubMethodName("sendNotifications")]
    public string SendNotifications()
    {
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        {               

            string query = @"SELECT COUNT(A.CODREF)QTDEAGENDSEMAGENDA FROM REGISTROS A INNER JOIN ATENDENTES U ON U.CODUSUARIO = A.CODUSUARIO WHERE A.CODUSUARIO = 11 AND A.STATUS IS NULL AND A.CODREF NOT IN ( SELECT CODREF FROM RETORNOS WHERE CODDIALOGO IS NULL AND AGEND_INTERNO IS NULL ) AND DATEDIFF(DAY, A.INICIO, GETDATE())> 11";

            connection.Open();

            using (SqlCommand command = new SqlCommand(query, connection))
            {

                command.Notification = null;

                DataTable dt = new DataTable();

                SqlDependency dependency = new SqlDependency(command);

                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                if (connection.State == ConnectionState.Closed)

                    connection.Open();

                var reader = command.ExecuteReader();

                dt.Load(reader);

                if (dt.Rows.Count > 0)
                {
                    qtdeAgendSemAgenda = (dt.Rows[0]["QTDEAGENDSEMAGENDA"].ToString());                        
                }

            }

        }

        IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();

        return Tratar.String(context.Clients.All.RecieveNotification(qtdeAgendSemAgenda));


    }