select b.工號
,b.姓名
,上班
,下班
,加班費時數
,加班起
,加班迄
,加班類別
FROM ((select EMPLOYEE_NO 工號
,EMPLOYEE_CNAME 姓名
,CARD_DATA_DATE 上班
,WORK_DATE
FROM vwZZ_CARD_DATA_MATCH
where convert(char,WORK_DATE,111) between @出勤開始日 and @出勤截止日
and WORK_CARD_TYPE='0'
) as A right join
(SELECT EMPLOYEE_NO 工號
,EMPLOYEE_CNAME 姓名
,CARD_DATA_DATE 下班
,WORK_DATE
FROM vwZZ_CARD_DATA_MATCH
where convert(char,WORK_DATE,111) between @出勤開始日 and @出勤截止日
and CARD_DATA_CODE='3'
and WORK_CARD_TYPE='1'
) AS b on (a.工號=b.工號 and convert(char,a.WORK_DATE,111)=convert(char,b.WORK_DATE,111))
right join
(select EMPLOYEE_NO 員工編號,
EMPLOYEE_CNAME 姓名,
ASK_LEAVE_START 加班起,
ASK_LEAVE_END 加班迄,
CHANGE_HOUR1 加班費時數,
LEAVE_SETUP_CLASS 加班類別
FROM vwZZ_ASK_LEAVE
where convert(char,ASK_LEAVE_START,111) between @出勤開始日 and @出勤截止日
and LEAVE_SETUP_CLASS like '%加班%'
) AS c on (a.工號=c.員工編號 and convert(char,a.WORK_DATE,111)=convert(char,c.加班起,111)
and convert(char,b.WORK_DATE,111)=convert(char,c.加班迄,111)))
- May 05 Tue 2015 13:36
SSRS mssql 語法範例二
全站熱搜
留言列表