猜猜，因为它有点难以阅读，但是对于所有这些案例陈述，只有一个可以提供答案而且它们不是相互排斥的 - 因此它在测试给出'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?