CREATE PROCEDURE AbsenteeReportCount(
    @customerparam INT,
    @groupparam INT,
    @personparam INT,
    @fromparam DATETIME,
    @toparam DATETIME,
    @daystartparam INT)
AS
BEGIN

    DECLARE @qry1 NVARCHAR(4000), @grpqry NVARCHAR(4000), @psnqry NVARCHAR(4000), @qry2 NVARCHAR(4000), @qry NVARCHAR(4000);

    SET @toparam = DATEADD(SECOND,86399,@toparam);

    SET @qry1 = 'SELECT COUNT(PC.cloud_date) FROM person PA ';

    SET @psnqry = CASE WHEN @personparam < 0 THEN
	' '
ELSE
	' AND PA.person_id = @personparam  '
END;

  SET @grpqry = CASE WHEN @groupparam < 0 THEN
	''
ELSE
	' INNER JOIN user_group_membership UGM ON PA.person_id = UGM.member AND UGM.end_date IS NULL AND UGM.assigned_group_id = @groupparam  '
END;

    SET @qry2 = '  CROSS JOIN cloud_date_table PC WHERE PA.customer_id = @customerparam AND PA.is_deleted = 0 AND (PA.person_type IS NULL OR PA.person_type = ''EMPLOYEE'') AND PC.cloud_date BETWEEN @fromparam AND @toparam ';

    SET @qry=concat(@qry1,@grpqry,@qry2,@psnqry);

    DECLARE @statement NVARCHAR(4000);
    SET @statement = @qry;
    DECLARE @parameterDefinition NVARCHAR(4000);
    SET @parameterDefinition = N'@customerparam INT, @groupparam INT, @personparam INT, @fromparam DATETIME, @toparam DATETIME, @daystartparam INT';
    EXEC sp_executesql @statement,@parameterDefinition,@customerparam=@customerparam,@groupparam=@groupparam,@personparam=@personparam,@fromparam=@fromparam,@toparam=@toparam,@daystartparam=@daystartparam;

END;

--Testcases
-- EXEC AbsenteeReportCount 1,-1,'2018-12-01', '2018-12-30', 0