Hi, I used the following code to update my table after each shift, But i noticed that when one column is updated the previous updated column is being cleared. How to fix this. Please help. Urgent..Thanks.declare @Weekday tinyint, @hour int select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate()) Update Weekly set Sat_Night =CASE WHEN (@Weekday= 1 and (@hour>= 7 AND @hour<19)) THEN ALD.EngTime ELSE NULL END,Sun_Day =CASE WHEN (@Weekday= 1 and (@hour >= 19 OR @hour < 7))THEN ALD.EngTime ELSE NULL END,Sun_Night =CASE WHEN (@Weekday= 2 and (@hour>= 7 AND @hour<19)) THEN ALD.EngTime ELSE NULL END,Mon_Day =CASE WHEN (@Weekday= 2 and (@hour >= 19 OR @hour < 7)) THEN ALD.EngTime ELSE NULL END,Mon_Night =CASE WHEN (@Weekday= 3 and (@hour>= 7 AND @hour<19))THEN ALD.EngTime ELSE NULL END,Tue_Day =CASE WHEN (@Weekday= 3 and (@hour >= 19 OR @hour < 7)) THEN ALD.EngTime ELSE NULL END,Tue_Night =CASE WHEN (@Weekday= 4 and (@hour>= 7 AND @hour<19)) THEN ALD.EngTime ELSE NULL END,Wed_Day =CASE WHEN (@Weekday= 4 and (@hour >= 19 OR @hour < 7))THEN ALD.EngTime ELSE NULL END,Wed_Night =CASE WHEN (@Weekday= 5 and (@hour>= 7 AND @hour<19)) THEN ALD.EngTime ELSE NULL END,Thu_Day =CASE WHEN (@Weekday= 5 and (@hour >= 19 OR @hour < 7)) THEN ALD.EngTime ELSE NULL END,Thu_Night =CASE WHEN (@Weekday= 6 and (@hour>= 7 AND @hour<19))THEN ALD.EngTime ELSE NULL END,Fri_Day =CASE WHEN (@Weekday= 6 and (@hour >= 19 OR @hour < 7)) THEN ALD.EngTime ELSE NULL END,Fri_Night =CASE WHEN (@Weekday= 7 and (@hour>= 7 AND @hour<19)) THEN ALD.EngTime ELSE NULL END,Sat_Day =CASE WHEN (@Weekday= 7 and (@hour >= 19 OR @hour < 7))THEN ALD.EngTime ELSE NULL END from ALD join Weekly on Weekly.TesterID = ALD.TesterID
Instead of NULL put the column as is:Update Weekly set Sat_Night =CASE WHEN (@Weekday= 1 and (@hour>= 7 AND @hour<19)) THEN ALD.EngTime ELSE Sat_Night END,Sun_Day =CASE WHEN (@Weekday= 1 and (@hour >= 19 OR @hour < 7))THEN ALD.EngTime ELSE Sun_Day END....... and so on
I hope your issue is now completely resolved after making hour and weekend checks adjustments as well.
Yes, but now Im in the following part of the project where I need to get the average of the columns once they are updated and then insert the average value of each columns into another table called AverageEngTime at column named Average. I guess I can use the following command but i dont know where to put it correctly in each cases. My AverageEngTime table consist of 2 columns, "Shifts" and "Average". Advice please.. Insert Into AverageEngTime(Average) SELECT AVG(Sat_Night) FROM `Weekly` where Shifts = 'Sat_Night' Update Weekly set Sat_Night=CASE WHEN (@Weekday= 1 and (@hour>= 7 AND @hour<19)) THEN ALD.EngTime ELSE Sat_Night END,
