You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
30 lines
1.2 KiB
30 lines
1.2 KiB
|
|
|
|
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|