DECLARE @startDate DATETIME;
SET @startDate = '20130301';
DECLARE @Keywords VARCHAR(8000)
DECLARE @Kwyword VARCHAR(255)
DECLARE cKeyword CURSOR FOR
WITH N(n) AS
(SELECT 0 UNION ALL SELECT n+1 FROM N WHERE n < 30)
SELECT
CONVERT(VARCHAR(10), DATEADD(dd,n,@startDate), 120)
FROM
N as t
WHERE
MONTH(DATEADD(dd,n,@startDate)) = MONTH(@startDate);
OPEN cKeyword
FETCH NEXT FROM cKeyword INTO @Kwyword
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Keywords = ISNULL( +@Keywords + ',', '') +'['+ @Kwyword +']'
FETCH NEXT FROM cKeyword INTO @Kwyword
END
CLOSE cKeyword
DEALLOCATE cKeyword
SELECT @Keywords AS Keywords
execute('select M.employee_v_email,* from (select M03_employee_i_employeeid,max(ESSL_AttendanceDate) as ESSL_AttendanceDate
,''In -''+CONVERT(VARCHAR(8), attendance_dt_intime, 108) + '' Out-''+ CONVERT(VARCHAR(8), attendance_dt_outtime, 108) attendance_dt_intime
from TRANS.T03_tblAttendance group by M03_employee_i_employeeid,attendance_dt_intime,
attendance_dt_outtime) tab
pivot (max(attendance_dt_intime) for ESSL_AttendanceDate in ('+@Keywords+')) as pv
inner join MTR.M03_tblemployee as M on pv.M03_employee_i_employeeid = M.employee_i_employeeid
order by pv.M03_employee_i_employeeid')
