The multi-part identifier "Weekly.Tester_ID" could

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

The multi-part identifier "Weekly.Tester_ID" could

Author

Topic

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-06 : 22:48:02

Hi, I used the following query to insert data into Mon_Day column when it is Monday morning from 7am to 6pm where the data should match the respective TesterID. I received "The multi-part identifier "Weekly.Tester_ID" could not be bound." error when i executed. Please advice..

declare @Weekday bit, @hour int
select @Weekday = case datepart(dw,getdate())
when 1 then 1 when 7 then 1 else 0 end ,@hour= datepart(hh,getdate())

if (@Weekday=2 and @hour between 7 and 18)
begin

Delete from Weekly --- Delete the old data

Insert Into Weekly(Mon_Day) --- Insert new data into Mon_Day column
SELECT EngTime FROM ALD --- from column EngTime from table ALD
where Weekly.Tester_ID = ALD.TesterID ---where TesterID of ALD matches TesterID of Weekly

end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-06 : 23:21:33

Insert Into Weekly(Mon_Day)
SELECT EngTime
FROM ALD
inner join Weekly w
where w.Tester_ID = ALD.TesterID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-06 : 23:30:36

Hi, thanks for reply, I receive this error when I execute your query.

Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'where'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-06 : 23:34:36

Oops, just change where to on. Copied and pasted your code and didn't see that mistake in there.

Insert Into Weekly(Mon_Day)
SELECT EngTime
FROM ALD
inner join Weekly w
on w.Tester_ID = ALD.TesterID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-06 : 23:40:05

i used this query,

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(Mon_Day)
SELECT EngTime
FROM ALD
inner join Weekly w
on w.TesterID = ALD.TesterID

end

It says Command(s) completed successfully. But i dont see the changes in the table..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-06 : 23:45:39

That's because you deleted the data, so now you don't have any records to match up in your join.

What exactly are you trying to transfer from ALD?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-06 : 23:52:21

Even if i remove the delete command it still says compilation successful but not giving output. What im trying to do is,
When the day is monday and the time is from 7am till 6pm, EngTime should from ALD should be pushed into Mon_Day column, If the day is monday and the time from 7pm till 6am (tuesday) the EngTime from ALD should be pushed into Mon_Night column and so on for the following days.. Please advice...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-06 : 23:56:49

I don't understand what you mean.

And yes of course if you remove the delete command it still doesn't show data. That's because there is nothing to match up in your join. You can't inner join to a table and expect results when one of the tables is empty.

Does ALD contain the data that you want in Weekly? If so,:

Insert Into Weekly(Mon_Day)
SELECT EngTime
FROM ALD

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-07 : 00:08:08

Ok.Let me put it properly.
ALD has shift report for every shift.
Let say today is Monday, so ALD will have Sunday Nights data until 7pm today. After 7pm today,ALD will have Monday mornings data.

So I need to grab that data and put it into Weekly table.
When it is Monday morning after 7am , I need Sunday Night's data to be in my Sun_Night column.
When it is Monday evening after 7pm, I need Monday Morning's data to be in my Mon_Day column and so on.

So i need to get time as well before inserting into the column. Can advice on how to do it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-07 : 00:47:27

quote:
Originally posted by shan1430

Ok.Let me put it properly.
ALD has shift report for every shift.
Let say today is Monday, so ALD will have Sunday Nights data until 7pm today. After 7pm today,ALD will have Monday mornings data.

So I need to grab that data and put it into Weekly table.
When it is Monday morning after 7am , I need Sunday Night's data to be in my Sun_Night column.
When it is Monday evening after 7pm, I need Monday Morning's data to be in my Mon_Day column and so on.

So i need to get time as well before inserting into the column. Can advice on how to do it?

Do Mon_Day,Sun_Night,etc represent count of your detail data?

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-07 : 01:38:14

I dont understand what you are asking. They are just column names..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-07 : 01:39:11