SQL Server Performance

Update a column without deleting other columns

Discussion in 'Getting Started' started by shan1430, Apr 8, 2008.

  1. shan1430 New Member

    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
  2. ranjitjain New Member

    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
  3. shan1430 New Member

    Thanks man, it works.
  4. ranjitjain New Member

    I hope your issue is now completely resolved after making hour and weekend checks adjustments as well.
  5. shan1430 New Member

    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,

Share This Page