CREATE PROCEDURE VisitAppointmentReportCount(@customerparam INT, @personparam INT, @scheduleparam INT, @appointmentparam INT, @fromparam DATETIME, @toparam DATETIME)
    AS
BEGIN

DECLARE @QRY NVARCHAR(4000),@PERSON_QRY NVARCHAR(4000),@ROSTER_QRY NVARCHAR(4000),@APPOINTMENT_QRY NVARCHAR(4000);

SET @PERSON_QRY = CASE WHEN (@personparam > 0) THEN ' AND V.visitor_id =  @personparam ' ELSE ' ' END;
SET @ROSTER_QRY = CASE WHEN (@scheduleparam > 0) THEN ' AND R.roster_id = @scheduleparam ' ELSE ' ' END;
SET @APPOINTMENT_QRY = CASE WHEN (@appointmentparam > 0) THEN ' AND VA.id = @appointmentparam ' ELSE ' ' END;

SET @QRY = CONCAT(

'SELECT
    COUNT(*)
FROM
    visitor_appointments VA
        INNER JOIN visit V ON
            VA.visit_id = V.id
        LEFT JOIN roster_item RI ON
            VA.attendant_roster_item_id = RI.roster_item_id
        LEFT JOIN roster R ON
            RI.roster_id = R.roster_id
WHERE
     VA.request_status = ''APPROVED'' AND
    VA.customer_id =@customerparam
    AND VA.appointment_start BETWEEN @fromparam AND @toparam'

, @PERSON_QRY, @ROSTER_QRY, @APPOINTMENT_QRY

);


DECLARE @parameterDefinition NVARCHAR(4000);
SET @parameterDefinition = N'@customerparam INT, @personparam INT, @scheduleparam INT, @appointmentparam INT, @fromparam DATETIME, @toparam DATETIME';
EXEC sp_executesql @QRY ,@parameterDefinition, @customerparam=@customerparam, @personparam=@personparam, @scheduleparam=@scheduleparam, @appointmentparam=@appointmentparam, @fromparam=@fromparam, @toparam=@toparam;

END