SELECT PROFILE_ID,CASE
WHEN PROFILE_ID = 'A001_C003' THEN 'aaaa'
WHEN PROFILE_ID = '4172_C003' THEN 'bbbb'
WHEN PROFILE_ID = '6020_C003' THEN 'cccc'
WHEN PROFILE_ID = '6104_C003' THEN 'dddd'
END
AS PROFILE_NAME, WORK_START_DATE, CASE
WHEN DATEDIFF(day, CREATE_DATE,WORK_START_DATE) - (select count(*) from asu_calendar as b
where a.work_start_date < b.day and b.day <a.oa_finish_date
and b.company_code = 'A001' and b.HOLIDAY_TYPE IS NOT NULL) >= 4
THEN '準時'
END
AS
DiffCreateDate,(select count(*) from asu_calendar as b where a.work_start_date < b.day and b.day <a.oa_finish_date
and b.company_code = 'A001' and b.HOLIDAY_TYPE IS NOT NULL) as holiday
FROM dbo.HR_PRE_USER AS a
WHERE (OA_TYPE <> 0) AND (HIRE_STATUS = 1)