Generate All days in moth report using PIVOT of SQL Server My table, tblEmployeeScan(EMPLOYEE_ID varchar(7), ScannedTime)

Everyday each employee scans 2 times, 1 time or  0 time

Now I want to create a stored procedure 

Create proc spMonthScanReport
    @MONTH int,
    @YEAR int


which returns a day all in month report like below :
The result you requested won't help you if you want to use this in any type of data set, cause the column names change with every call, a better solution is to use static column names from 1 - 31 (You might not need dynamic sql in this case), but that is up to your need, maybe you just want to export them directly.

And one more thing, you didn't specify the conditions for scan numbers, what if a user scanned more than 2 times a day, what if he didn't scan the whole month, he won't even appear in the results (and to show him, it requires a different query).

USE [YOUR_DATABASE];

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Ahmad Magdy
-- Create date: 2014-10-10 13:10
-- Description: 
-- =============================================
CREATE PROCEDURE [PROCEDURE_NAME]
    @Year INT = NULL,
    @Month INT = NULL
AS
BEGIN
SET NOCOUNT ON;

SET @YEAR = CASE WHEN @Year IS NULL THEN YEAR(GETDATE()) ELSE @Year END;
SET @Month = CASE WHEN @Month IS NULL OR @Month NOT BETWEEN 1 AND 12 THEN MONTH(GETDATE()) ELSE @Month END;

DECLARE @FirstDayOfMonth DATE = CAST(@Year AS VARCHAR(4)) + '-' + CASE WHEN @Month &lt; 10 THEN '0' ELSE '' END + CAST(@Month AS VARCHAR(2)) + '-' + '01';
DECLARE @FirstDayOfNextMonth DATE = DATEADD(MONTH, 1, @FirstDayOfMonth);
DECLARE @LastDayOfMonth DATE = DATEADD(DAY, -1, @FirstDayOfNextMonth);

DECLARE @SQLCode NVARCHAR(MAX) =
'SELECT *
FROM
(   SELECT ap.EMPLOYEE_ID, ap.ScannedTime, CONVERT(DATE, ap.ScannedTime) AS Date, ''SCN'' + CAST(ROW_NUMBER() OVER (PARTITION BY ap.EMPLOYEE_ID, CONVERT(DATE, ap.ScannedTime) ORDER BY ap.ScannedTime ASC) AS VARCHAR) AS Type
    FROM tblEmployeeScan ap
    WHERE ap.ScannedTime &gt;= ''' + CAST(@FirstDayOfMonth AS VARCHAR) + ''' AND ap.ScannedTime &lt; ''' + CAST(@FirstDayOfNextMonth AS VARCHAR) + ''' -- You can use YEAR(ap.ScannedTime) = @Year AND MONTH(ap.ScannedTime) = @Month, but that will not make this query Sargable
) pv
PIVOT
(
    MAX(ScannedTime)
    FOR Date IN (';
DECLARE @i DATE = @FirstDayOfMonth;
WHILE(@i &lt;= @LastDayOfMonth)
BEGIN
    SET @SQLCode = @SQLCode + '[' + CAST(@i AS NVARCHAR) + '], ';
    SET @i = DATEADD(DAY, 1, @i);
END
SET @SQLCode = LEFT(@SQLCode, LEN(@SQLCode) - 1);
SET @SQLCode = @SQLCode + ')
) p
--WHERE Type = ''SCN1'' OR Type = ''SCN2'' -- TODO You can use this query to limit the scans to 2
ORDER BY EMPLOYEE_ID, Type; --OPTION (RECOMPILE)';

PRINT CAST(@SQLCode AS NTEXT);

EXECUTE SP_EXECUTESQL @SQLCode;
END
GO