如何使用此代码更新出勤率 [英] How Can I Update Attendance Using This Code

问题描述

声明@PDate datetime，

@Hdate datetime，

@In_Punch datetime，

@Out_Punch datetime



UPDATE MASTERPROCESSDAILYDATA SET状态=转换（时间，In_Punch）在'08：00'和'09：30'之间的情况和

转换（时间，Out_Punch）之间的情况'16：00'和'17：30'然后'P'


转换时的
（时间，In_Punch）> '09：30'或转换（时间，In_Punch）< '09：30'AND转换（时间，Out_Punch）<> NULL或转换（time，Out_Punch）< = '15：30'或转换（time，In_Punch）< '09：30'AND转换（时间，Out_Punch）<> NULL或转换（time，Out_Punch）在'08：00'和'17：30'之间转换（time，In_Punch）= NULL或

转换（时间，In_Punch）在'08：00'之间和'17：30'并转换（时间，Out_Punch）= NULL然后'HL'



当（转换（时间，Out_Punch）在'08：00'之间时'17：30'并转换（时间，In_Punch）= NULL）或（转换（时间，In_Punch）在'08：00'和'17：30'之间转换（time，Out_Punch）= NULL）或转换（时间， Out_Punch）< = '15：30'然后'HL'



当Status ='WO'然后'WO'



当Status ='H'然后'H'



当Status ='MS'然后'HL'

转换时（时间，In_Punch）<> NULL和转换（time，Out_Punch）= NULL然后'HL'



转换时（时间，In_Punch）= NULL和转换（时间，Out_Punch）<> NULL然后'HL'







- 当状态='A'和转换（时间，在'08：00'和'09：30'之间的In_Punch）和转换（时间，Out_Punch）= NULL然后'HL'< code>< pre>< pre lang =SQL> ;< big>< / big>< / pre>< / pre>


当In_Punch<>时，
NULL和Out_Punch<> NULL和@PDate = @Hdate然后'HP'



转换（时间，In_Punch）在'08：00'和'17：30'之间转换（时间） ，Out_Punch）= NULL然后'HL'



当status ='WO'和In_Punch<> NUll AND Out_Punch<> NULL然后'WOP'



当In_Punch！= NUll AND Out_Punch！= NULL和@PDate = @Hdate然后'HP'

< br $> b $ b

else'A'

end

当我尝试执行此代码时，状态不会更新时假期被分配，并且有拳头在那里。它显示状态为'P'但是，我想要'HP'





任何人都可以提供帮助我来解决这个问题。





谢谢

解决方案

猜猜，因为它有点难以阅读，但是对于所有这些案例陈述，只有一个可以提供答案而且它们不是相互排斥的 - 因此它在测试给出'HP'的条件之前达到提供'P'的条件br />


所以将'when'案例移动到你实际需要测试它们的顺序 - 可能首先测试HP案例？

declare @PDate datetime,
@Hdate datetime,
@In_Punch datetime,
@Out_Punch datetime

UPDATE MASTERPROCESSDAILYDATA SET Status = case when convert(time,In_Punch) between '08:00'and '09:30' and
convert(time,Out_Punch) between '16:00' and '17:30' then 'P'

when convert(time,In_Punch) > '09:30' or convert(time,In_Punch) < '09:30' AND convert(time,Out_Punch) <> NULL or convert(time,Out_Punch) <= '15:30' or convert(time,In_Punch) < '09:30' AND convert(time,Out_Punch) <> NULL OR convert(time,Out_Punch) between '08:00' and '17:30' and convert(time,In_Punch) = NULL OR
convert(time,In_Punch) between '08:00'and '17:30' and convert(time,Out_Punch) = NULL then 'HL'

when (convert(time,Out_Punch) between '08:00' and '17:30' and convert(time,In_Punch) = NULL) OR (convert(time,In_Punch) between '08:00'and '17:30' and convert(time,Out_Punch) = NULL) OR convert(time,Out_Punch) <= '15:30' then 'HL'

when Status = 'WO' then 'WO'

when Status = 'H' then 'H'

when Status = 'MS' then 'HL'
when convert(time,In_Punch) <> NULL AND convert(time,Out_Punch) = NULL then 'HL'

when convert(time,In_Punch) = NULL AND convert(time,Out_Punch) <> NULL then 'HL'



--when Status = 'A' AND convert(time,In_Punch) between '08:00'and '09:30' and convert(time,Out_Punch) = NULL then 'HL'<code><pre><pre lang="SQL"><big></big></pre></pre>

when In_Punch <> NULL AND Out_Punch <> NULL AND @PDate = @Hdate then 'HP'

when convert(time,In_Punch) between '08:00'and '17:30' and convert(time,Out_Punch) = NULL then 'HL'

when status = 'WO' And In_Punch <> NUll AND Out_Punch <> NULL then 'WOP'

when In_Punch != NUll AND Out_Punch != NULL AND @PDate = @Hdate then 'HP'


else'A'
end

When i am trying to excecute this code thestatus is not updated when holiday is assigned and punches is there.It shows Status as 'P' but,i want 'HP'


Can any one help me to solve this problem.


Thanks

解决方案

Just guessing, as it's kinda hard to read , but with all those case statements, only one can supply an answer and they are not mutually exclusive - so it is hitting a condition that provides a 'P' before testing the condition that gives 'HP'

So move your 'when' cases into the order you actually need to test them - probably testing the HP case first?

这篇关于如何使用此代码更新出勤率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！