declare @StartDate as DATE, @EndDate as DATE,@week VARCHAR(7),@leave as DATE
set @StartDate = dateadd(day,-30,GETDATE())
set @EndDate =GETDATE()
set @week =(SELECT TOP 1 WeekDays FROM OTB_HR_AttendanceDetail WHERE AttGuid=(SELECT  [Guid] FROM OTB_HR_Attendance WHERE [Guid] =(SELECT AskLeaveGuid FROM dbo.OTB_SYS_Members WHERE MemberID='john.yuan')) ORDER BY TimeClass )
;
WITH  dtshow(dt,weekdays)
AS (
select CONVERT(VARCHAR(100), dateadd(day,number,@StartDate), 111) AS dt,datepart(weekday, dateadd(day,number,@StartDate)) AS weekdays
from master.dbo.spt_values  where type ='P'
and number <=DATEDIFF(day,@StartDate,@EndDate) AND CHARINDEX(CONVERT(VARCHAR(1), datepart(weekday, dateadd(day,number,@StartDate))),@week)>0
AND CONVERT(VARCHAR(100), dateadd(day,number,@StartDate), 111) NOT IN  (SELECT CONVERT(VARCHAR(100), CreateDate, 111) FROM dbo.OTB_HR_AskLeave WHERE (CreateDate BETWEEN @StartDate AND @EndDate) AND CreateUser='john.yuan')
)
SELECT DISTINCT dt,weekdays,AMWarn,PMWarn,dt AS TipsID,'DayWork' AS PageID,'' AS PKID,'N' AS IsRead,dt +'未填寫工作彙報，請點擊進行編輯' AS TipsName FROM OTB_MNG_DayWork W RIGHT JOIN dtshow D ON CONVERT(VARCHAR(100), W.StartDate, 111)=D.dt WHERE AMWarn IS NULL OR   PMWarn  IS NULL