Cant get the output

Hi, I used the following query to select and insert data from ALD table into Weekly table by shift.
When it is Monday morning,data should be pushed into Mon_Day,
When it is Monday evening,data should be pushed into Mon_Night
with respect to the TesterID that is selected in Monday Morning and followed by other days..
example of output that I want:
TesterId Mon_Day Mon_Night
CMT1 15 20
CMT2 26 65
But i get the result for monday morning but not for monday night. It says Msg 4104, Level 16, State 1, Line 20
The multi-part identifier "Weekly.TesterID" could not be bound. .....
. Advice please...
declare @Weekday bit, @hour int
select
@Weekday = case datepart(dw,getdate())when 1 then 1 when 7 then 7 else 0 end ,@hour= datepart(hh,getdate()) if
(@Weekday=2 and @hour between 7 and 18)begin
Delete from Weekly Insert
Into Weekly(TesterID,Mon_Day)SELECT
TesterID,EngTime FROM ALD where
TesterID LIKE 'C%'end
if
(@Weekday=2 and (@hour > 18 or @hour < 7))begin
Insert
Into Weekly(TesterID,Mon_Night)SELECT
TesterID,EngTime FROM ALD where
Weekly.TesterID = ALD.TesterIDend

I guess you are trying to join Weekly table and ALD table but have not specified that table.
try this:
Insert Into Weekly(TesterID,Mon_Night)
SELECT TesterID,EngTime FROM ALD JOIN Weekly
on Weekly.TesterID = ALD.TesterID
If you are trying to insert new values for existing testerID in your table then above query will work, but if you want to just update column values for existing testerid then try Update statement.

Yes I am trying to update the column with existing TesterID. How to use Update commands when I need to grab data from EngTime column from ALD table? Advice please...

UPDATE Weekly set mon_night=ald.engtime
FROM ALD JOIN Weekly ON weekly.testerid=ald.testerid

Hi, thanks for helping..Now I have a new problem. I used the following command but i think the query not detecting the day of the week so it fills all the day's_night column. Advice..declare @Weekday bit, @hour int
select
@Weekday = case datepart(dw,getdate())when
1 then 1 when 7 then 7 else 0 end ,@hour= datepart(hh,getdate()) if
(@Weekday= 1 and @hour> 7 or @hour<=19)begin
Update
Weekly set Sat_Night=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 1 and @hour > 18 or @hour < 7)begin
Update
Weekly set Sun_Day=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 2 and @hour> 7 or @hour<=19)begin
Update
Weekly set Sun_Night=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 2 and @hour > 18 or @hour < 7)begin
Update
Weekly set Mon_Day=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 3 and @hour> 7 or @hour<=19)begin
Update
Weekly set Mon_Night=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 3 and @hour > 18 or @hour < 7)begin
Update
Weekly set Tue_Day=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 4 and @hour> 7 or @hour<=19)begin
Update
Weekly set Tue_Night=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 4 and @hour > 18 or @hour < 7)begin
Update
Weekly set Wed_Day=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 5 and @hour> 7 or @hour<=19)begin
Update
Weekly set Wed_Night=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 5 and @hour > 18 or @hour < 7)begin
Update
Weekly set Thu_Day=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 6 and @hour> 7 or @hour<=19)begin
Update
Weekly set Thu_Night=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 6 and @hour > 18 or @hour < 7)begin
Update
Weekly set Fri_Day=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 7 and @hour> 7 or @hour<=19)begin
Update
Weekly set Fri_Night=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend
if
(@Weekday= 7 and @hour > 18 or @hour < 7)begin
Update
Weekly set Sat_Day=ALD.EngTimefrom
ALD join Weekly on Weekly.TesterID = ALD.TesterIDend

select @Weekday = case datepart(dw,getdate())
when 1 then 1 when 7 then 7 else 0 end
from this statemnt @Weekday will never hold value greater than 1 whereas you are comparing >1 in IF conditions.
If I have understood your quest than I guess you have day,night columns for all days in the table. Based on day of week you want to update the corresponding column.
Can you elaborate how are you getting the rows i.e. testerid in weekly table at the first glance.
Please post the complete scenario once again.

ALD is a table where it will be updated automatically for every shift. Weekly table should grab the data and put it into correct column. For example,
When the day is Monday and the time is 7.00am - 6.59pm, ALD will have Sunday Night's shift data. So this data must be put into Sun_Night column in Weekly table.
When the day is Monday and the time is 7.00pm - 6.59am(Tuesday), ALD will have Monday Day's shift data. So this data must be put into Mon_Day column in Weekly table. and so on for the other days in the week.
So what I did here is I preset the Tester Ids in TesterID column and then trying to update the Days and Nights columns with the respective data.
Any advice on this...Please help. Urgent. Thank you...

Check this:declare
@Weekday bit, @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 > 18 AND @hour < 7) THEN ALD.EngTime ELSE NULL END,Sun_Night
=CASE WHEN (@Weekday= 2 and @hour> 7 or @hour<=19) THEN ALD.EngTime ELSE NULL ENDand
so on.....from
ALD join Weekly on Weekly.TesterID = ALD.TesterID
Here based on the weekday and hour of the day update the corresponding column

Hi, thanks for the help.. I used the following code as you gave and i get my sat_night column filled, But since today is tuesday the mon_night column suppose to be filled. Any advice?declare
@Weekday bit, @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 > 18 AND @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 > 18 AND @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 > 18 AND @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 > 18 AND @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 > 18 AND @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 > 18 AND @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 > 18 AND @hour < 7))THEN ALD.EngTime ELSE NULL END
from
ALD join Weekly on Weekly.TesterID = ALD.TesterID

Hello can anyone help me on above issue please..

You need to consider the fact that at 00:00 hour the weekday will change, so you need to make corresponding adjustments in your weekday checks and hours accordingly. Otherwise try to explain this updatation rules with some sample data.

Thanks for the input, now i used the following and it works..
Sat_Day=CASE WHEN (@Weekday= 7 and @hour >= 19 ) OR (@Weekday= 1 AND @hour <7))