select AttenDanceid, sum(case when ((mintime is null and mitime is not null and mitime < 规定上午上班时间 ) or (mintime is not null and mintime < 规定上午上班时间)) or ((mouttime is null and motime is not null and mitime < 规定上午下班时间 ) or (mintime is not null and mintime < 规定上午下班时间)) or ((aintime is null and aitime is not null and aitime < 规定下午上班时间 ) or (aintime is not null and aintime < 规定下午下班时间) )or ((aouttime is null and datetime is not null and datetime < 规定下午下班时间 ) or (aouttime is not null and aouttime< 规定下午下班时间)) then 1 else 0 end) [正常上班累计工作天数], sum(datediff(h,ADDTIME,ENDTIME ) [累计加班时间 ], sum(datediff(h,LSTARDATE,LENDDATE) [累计请假时间 ], sum(datediff(h,EENDDATE,ESTARTDATE) [累计出差时间 ], case when ((mintime is null and mitime is not null and mitime > 规定上午上班时间) or (mintime is not null and mintime > 规定上午上班时间) ) or ((aintime is not null and aintime > 规定下午上班时间 ) or (aintime is null and aintime > 规定下午上班时间 )) then 1 else 0 end [迟到次数 ], case when ((mouttime is null and motime is not null and mitime > 规定上午下班时间 ) or (mintime is not null and mintime > 规定上午下班时间)) or ((aouttime is null and datetime is not null and datetime > 规定下午下班时间 ) or (aouttime is not null and aouttime > 规定下午下班时间)) then 1 else 0 end [早退次数 ], case when mitime is null and motime is null and aotime is null then 1 else 0 end [旷工次数] from Attendance where month (mitime) = '要查询的月份'
上面更正一下select AttenDanceid, sum(case when ((mintime is null and mitime is not null and mitime < 规定上午上班时间 ) or (mintime is not null and mintime < 规定上午上班时间)) and ((mouttime is null and motime is not null and mitime < 规定上午下班时间 ) or (mintime is not null and mintime < 规定上午下班时间)) and ((aintime is null and aitime is not null and aitime < 规定下午上班时间 ) or (aintime is not null and aintime < 规定下午下班时间)) and ((aouttime is null and datetime is not null and datetime < 规定下午下班时间 ) or (aouttime is not null and aouttime< 规定下午下班时间)) then 1 else 0 end) [正常上班累计工作天数], sum(datediff(h,ADDTIME,ENDTIME ) [累计加班时间 ], sum(datediff(h,LSTARDATE,LENDDATE) [累计请假时间 ], sum(datediff(h,EENDDATE,ESTARTDATE) [累计出差时间 ], case when ((mintime is null and mitime is not null and mitime > 规定上午上班时间) or (mintime is not null and mintime > 规定上午上班时间) ) or ((aintime is not null and aintime > 规定下午上班时间 ) or (aintime is null and aintime > 规定下午上班时间 )) then 1 else 0 end [迟到次数 ], case when ((mouttime is null and motime is not null and mitime > 规定上午下班时间 ) or (mintime is not null and mintime > 规定上午下班时间)) or ((aouttime is null and datetime is not null and datetime > 规定下午下班时间 ) or (aouttime is not null and aouttime > 规定下午下班时间)) then 1 else 0 end [早退次数 ], case when mitime is null and motime is null and aotime is null then 1 else 0 end [旷工次数] from Attendance where month (mitime) = '要查询的月份'
sum(case when
((mintime is null and mitime is not null and mitime < 规定上午上班时间 ) or
(mintime is not null and mintime < 规定上午上班时间)) or
((mouttime is null and motime is not null and mitime < 规定上午下班时间 ) or
(mintime is not null and mintime < 规定上午下班时间)) or
((aintime is null and aitime is not null and aitime < 规定下午上班时间 ) or
(aintime is not null and aintime < 规定下午下班时间) )or
((aouttime is null and datetime is not null and datetime < 规定下午下班时间 ) or
(aouttime is not null and aouttime< 规定下午下班时间))
then 1 else 0 end) [正常上班累计工作天数],
sum(datediff(h,ADDTIME,ENDTIME ) [累计加班时间 ],
sum(datediff(h,LSTARDATE,LENDDATE) [累计请假时间 ],
sum(datediff(h,EENDDATE,ESTARTDATE) [累计出差时间 ],
case when
((mintime is null and mitime is not null and mitime > 规定上午上班时间) or
(mintime is not null and mintime > 规定上午上班时间) ) or
((aintime is not null and aintime > 规定下午上班时间 ) or
(aintime is null and aintime > 规定下午上班时间 ))
then 1 else 0 end [迟到次数 ],
case when
((mouttime is null and motime is not null and mitime > 规定上午下班时间 ) or
(mintime is not null and mintime > 规定上午下班时间)) or
((aouttime is null and datetime is not null and datetime > 规定下午下班时间 ) or
(aouttime is not null and aouttime > 规定下午下班时间))
then 1 else 0 end [早退次数 ],
case when mitime is null and motime is null and aotime is null
then 1 else 0 end [旷工次数]
from Attendance
where month (mitime) = '要查询的月份'
sum(case when
((mintime is null and mitime is not null and mitime < 规定上午上班时间 ) or
(mintime is not null and mintime < 规定上午上班时间)) and
((mouttime is null and motime is not null and mitime < 规定上午下班时间 ) or
(mintime is not null and mintime < 规定上午下班时间)) and
((aintime is null and aitime is not null and aitime < 规定下午上班时间 ) or
(aintime is not null and aintime < 规定下午下班时间)) and
((aouttime is null and datetime is not null and datetime < 规定下午下班时间 ) or
(aouttime is not null and aouttime< 规定下午下班时间))
then 1 else 0 end) [正常上班累计工作天数],
sum(datediff(h,ADDTIME,ENDTIME ) [累计加班时间 ],
sum(datediff(h,LSTARDATE,LENDDATE) [累计请假时间 ],
sum(datediff(h,EENDDATE,ESTARTDATE) [累计出差时间 ],
case when
((mintime is null and mitime is not null and mitime > 规定上午上班时间) or
(mintime is not null and mintime > 规定上午上班时间) ) or
((aintime is not null and aintime > 规定下午上班时间 ) or
(aintime is null and aintime > 规定下午上班时间 ))
then 1 else 0 end [迟到次数 ],
case when
((mouttime is null and motime is not null and mitime > 规定上午下班时间 ) or
(mintime is not null and mintime > 规定上午下班时间)) or
((aouttime is null and datetime is not null and datetime > 规定下午下班时间 ) or
(aouttime is not null and aouttime > 规定下午下班时间))
then 1 else 0 end [早退次数 ],
case when mitime is null and motime is null and aotime is null
then 1 else 0 end [旷工次数]
from Attendance
where month (mitime) = '要查询的月份'