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