SQL Server Performance

Cant get the output

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

  1. shan1430 New Member

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

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

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

    UPDATE Weekly set mon_night=ald.engtime
    FROM ALD JOIN Weekly ON weekly.testerid=ald.testerid
  5. shan1430 New Member

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

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

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

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

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

    Hello can anyone help me on above issue please..
  11. ranjitjain New Member

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

    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))

Share This Page