sql case 嵌套

最新推荐文章于 2023-04-07 09:05:45 发布

阿yoyo

最新推荐文章于 2023-04-07 09:05:45 发布

阅读量1w

点赞数 1

本文链接：https://blog.csdn.net/LisaTao/article/details/4324820

版权

/*************************************************************************************************************/
--计算非固定薪资人员加班小时
/*************************************************************************************************************/
CREATE Proc RecountWorkHourForDF
@BeginDate varchar(10),
@EndDate varchar(10)
AS
-------------先删除操作日期的加班记录------------------------------------------------
delete from WorkHourDetail
Where (CardDate between @BeginDate and @EndDate )
and (left(CDay,1)='D' or left(CDay,1)='F')

insert into WorkHourDetail(ECode,CardDate,CDay,FactWorkHour)
Select -- A.ECode,A.EName,A.Card1,A.Card2,A.addtime,B.BTIME,B.ETIME,CDay,a.CardDate,
A.ECode,A.CardDate,B.CDay,
Case when substring(CDay,2,1)='*' then
------------------------------正常上班前打上班卡--------------------------------------
case when (card1<=B.BTIME and card1>'12:00' )then
case when (card2<B.SETIME and card2>'12:00') then
case when (datediff(mi,B.BTIME,card2)%60)>=30 then
cast((datediff(mi,B.BTIME,card2)/60) as varchar(2))+'.5'
else
cast((datediff(mi,B.BTIME,card2)/60) as varchar(2))+'.0'
end
when ((card2>=B.SETIME) and (card2<'24:00') and (left(CDay,1)='F') ) then
'4.0'
when ((card2>=B.XBTIME) and (card2<B.ETIME)) then
case when (datediff(mi,B.XBTIME,card2)%60)>=30 then
cast(((datediff(mi,B.XBTIME,card2)+240)/60) as varchar(2))+'.5'
else
cast(((datediff(mi,B.XBTIME,card2)+240)/60) as varchar(2))+'.0'
end
when ((card2>=B.ETIME) and (card2<=B.BWorkTime)) then
'8.0'
when (card2>B.BWorkTime and card2<'12:00')then
case when (datediff(mi,B.BWorkTime,card2)%60)>=30 then
cast(((datediff(mi,B.BWorkTime,card2)+480)/60) as varchar(2))+'.5'
else
cast(((datediff(mi,B.BWorkTime,card2)+480)/60) as varchar(2))+'.0'
end
else
'0.0'
end
-----------------------------上半天下班前打上班卡------------------------------------
when ((card1>=B.BTIME) and (card1<B.SETIME ) ) then
case when (card2<B.SETIME and card2>'12:00' ) then
case when (datediff(mi,BeginCard,card2)%60)>=30 then
cast((datediff(mi,BeginCard,card2)/60) as varchar(2))+'.5'
else
cast((datediff(mi,BeginCard,card2)/60) as varchar(2))+'.0'
end
when ((card2>=B.SETIME) and (card2<'24:00')) then
case when (datediff(mi,BeginCard,B.SETIME)%60)>=30 then
cast((datediff(mi,BeginCard,B.SETIME)/60) as varchar(2))+'.5'
else
cast((datediff(mi,BeginCard,B.SETIME)/60) as varchar(2))+'.0'
end
when ((card2>B.XBTIME) and (card2<B.ETIME) and (card2<'12:00')) then
case when ((datediff(mi,BeginCard,B.SETIME)+datediff(mi,B.XBTIME,card2))%60)>=30 then
cast(((datediff(mi,BeginCard,B.SETIME)+datediff(mi,B.XBTIME,card2))/60) as varchar(2))+'.5'
else
cast(((datediff(mi,BeginCard,B.SETIME)+datediff(mi,B.XBTIME,card2))/60) as varchar(2))+'.0'
end
when ((card2>=B.ETIME) and (card2<=B.BWorkTime)) then
case when ((datediff(mi,BeginCard,B.SETIME)+240)%60)>=30 then
cast(((datediff(mi,BeginCard,B.SETIME)+240)/60) as varchar(2))+'.5'
else
cast(((datediff(mi,BeginCard,B.SETIME)+240)/60) as varchar(2))+'.0'
end
when (card2>B.BWorkTime and card2<'12:00') then
case when ((datediff(mi,BeginCard,B.SETIME)+240+datediff(mi,B.BWorkTime,card2))%60)>=30 then
cast(((datediff(mi,BeginCard,B.SETIME)+240+datediff(mi,B.BWorkTime,card2))/60) as varchar(2))+'.5'
else
cast(((datediff(mi,BeginCard,B.SETIME)+240+datediff(mi,B.BWorkTime,card2))/60) as varchar(2))+'.0'
end
else
'0.0'
end
-----------------------------下半天上班前打上班卡------------------------------------
when (card1>=B.SETIME and card1>'12:00') then
case when ((card2>B.XBTIME) and (card2<B.ETIME)) then
case when (datediff(mi,B.XBTIME,card2)%60)>=30 then
cast((datediff(mi,B.XBTIME,card2)/60) as varchar(2))+'.5'
else
cast((datediff(mi,B.XBTIME,card2)/60) as varchar(2))+'.0'
end
when ((card2>=B.ETIME) and (card2<=B.BWorkTime)) then
'4.0'
when (card2>B.BWorkTime and card2<'12:00') then
case when ((240+datediff(mi,B.BWorkTime,card2))%60)>=30 then
cast(((240+datediff(mi,B.BWorkTime,card2))/60) as varchar(2))+'.5'
else
cast(((240+datediff(mi,B.BWorkTime,card2))/60) as varchar(2))+'.0'
end
else
'0.0'
end
-----------------------------下半天下班前打上班卡------------------------------------
when (card1>=B.XBTIME and card1<B.ETIME and card1<'12:00') then
case when (card2<B.ETIME) then
case when (datediff(mi,BeginCard,card2)%60)>=30 then
cast((datediff(mi,BeginCard,card2)/60) as varchar(2))+'.5'
else
cast((datediff(mi,BeginCard,card2)/60) as varchar(2))+'.0'
end
when ((card2>=B.ETIME) and (card2<=B.BWorkTime)) then
case when (datediff(mi,BeginCard,B.ETIME)%60)>=30 then
cast((datediff(mi,BeginCard,B.ETIME)/60) as varchar(2))+'.5'
else
cast((datediff(mi,BeginCard,B.ETIME)/60) as varchar(2))+'.0'
end
when (card2>B.BWorkTime and card2<'12:00') then
case when ((datediff(mi,BeginCard,card2)-datediff(mi,B.ETIME,B.BWorkTime))%60)>=30 then
cast(((datediff(mi,BeginCard,card2)-datediff(mi,B.ETIME,B.BWorkTime))/60) as varchar(2))+'.5'
else
cast(((datediff(mi,BeginCard,card2)-datediff(mi,B.ETIME,B.BWorkTime))/60) as varchar(2))+'.0'
end
else
'0.0'
end
when (Card1>=B.ETIME and Card1<=B.BWorkTime) then
case when (card2>B.BWorkTime) then
case when (datediff(mi,B.BWorkTime,card2)%60)>=30 then
cast((datediff(mi,B.BWorkTime,card2)/60) as varchar(2))+'.5'
else
cast((datediff(mi,B.BWorkTime,card2)/60) as varchar(2))+'.0'
end
else
'0.0'
end
when (Card1>B.BWorkTime) then
case when (card2>Card1 and card2>B.BWorkTime) then
case when (datediff(mi,BeginCard,card2)%60)>=30 then
cast((datediff(mi,BeginCard,card2)/60) as varchar(2))+'.5'
else
cast((datediff(mi,BeginCard,card2)/60) as varchar(2))+'.0'
end
else
'0.0'
end

else
'0.0'
end
else
case when ((datediff(mi,B.BWorkTime,card2)>=30) and card2<='12:00') then
case when ((datediff(mi,B.BWorkTime,card2))%60)>=30 then
cast(((datediff(mi,B.BWorkTime,card2))/60) as varchar(2))+'.5'
else
cast(((datediff(mi,B.BWorkTime,card2))/60) as varchar(2))+'.0'
end
else
'0.0'
end
end FactWorkHour
From CardRecord A,EmpClass B
Where A.Ecode=B.WORKID and A.CardDate=B.PRODATE --AND A.Ecode in (Select Ecode From Emploees Where EMoneyFix=0)
and (A.CardDate between @BeginDate and @EndDate)
and (left(CDay,1)='D' or left(CDay,1)='F')
-- and A.CardDate in ('2007-12-01','2007-12-02','2007-12-06','2007-12-08','2007-12-09')

GO