Multi-Part Identifier Error - SQL Server Forums

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

Multi-Part Identifier Error

Author

Topic

creed65
Starting Member

2 Posts

Posted - 2011-05-11 : 12:34:25

I have working on this issue and looking at boards but have not been able to fix this problem. Below are the errors I am receiving and the code is below the errors. Any help would be greatly appreciated.
------------------------------------------------------------------
Msg 4104, Level 16, State 1, Line 34
The multi-part identifier "##TempSOSLA.DateReceived" could not be bound.
Msg 4104, Level 16, State 1, Line 35
The multi-part identifier "##TempSOSLA.DateOpened" could not be bound.
Msg 4104, Level 16, State 1, Line 36
The multi-part identifier "##TempSOSLA.DateReceived" could not be bound.
Msg 4104, Level 16, State 1, Line 36
The multi-part identifier "##TempSOSLA.TimeReceived" could not be bound.
Msg 4104, Level 16, State 1, Line 37
The multi-part identifier "##TempSOSLA.DateOpened" could not be bound.
Msg 4104, Level 16, State 1, Line 37
The multi-part identifier "##TempSOSLA.TimeOpened" could not be bound.
Msg 4104, Level 16, State 1, Line 38
The multi-part identifier "##TempSOSLA.Priority" could not be bound.
---------------------------------------------------------------------
DECLARE @iRowCnt INT
DECLARE @Index INT
DECLARE @daterec DATETIME
DECLARE @dateopen DATETIME
DECLARE @datetimerec DATETIME
DECLARE @datetimeopen DATETIME
DECLARE @calcdatetime DATETIME
DECLARE @firstday INT
DECLARE @minutes INT
DECLARE @priority VARCHAR(30)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT SONumber,ContractNumber,Priority, DateReceived ,TimeReceived,DateOpened,TimeOpened,DATEDIFF(mi,CAST(CAST(DateReceived AS DATE)AS DATETIME)+CAST(TimeReceived as TIME),CAST(CAST(DateOpened AS DATE)AS DATETIME)+CAST(TimeOpened as TIME)) as NumofMinsBeforeOpened
INTO ##TempSOHours
FROM tblServiceOrders
WHERE DateClosed is null and (ContractNumber is not null and ContractNumber<>0 and ContractNumber<>1) and DateOpened is not null and TimeOpened is not null and (Priority like '%High%' or Priority like '%Medium%') ORDER BY DateReceived DESC
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE ##TempSOHours
ADD MinsExcludeWeekend VARCHAR(255)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
set @Index=1
set @firstday=1

SELECT SONumber,ContractNumber,Priority,DateReceived,DateOpened,TimeReceived,TimeOpened, NumofMinsBeforeOpened,MinsExcludeWeekend, row_number() OVER (ORDER BY SoNumber ASC) AS RowID
INTO ##TempSOSLA
FROM ##TempSOHours

SELECT @iRowCnt = COUNT(SoNumber) FROM ##TempSOSLA
--set @iRowCnt=@@ROWCOUNT

WHILE (@Index <= @iRowCnt)
BEGIN
SELECT * FROM ##TempSOSLA WHERE RowID = @Index

set @daterec = ##TempSOSLA.DateReceived
set @dateopen = ##TempSOSLA.DateOpened
set @datetimerec = ##TempSOSLA.DateReceived + ##TempSOSLA.TimeReceived
set @datetimeopen = ##TempSOSLA.DateOpened + ##TempSOSLA.TimeOpened
set @priority=##TempSOSLA.Priority

WHILE(@daterec<=@dateopen)
BEGIN
IF(@priority IN ('High 24x7','Medium 24x7'))
BEGIN
IF(@firstday=1 and (@daterec<>@dateopen) and DATEPART(DW,@daterec) IN (1,5,6,7))
BEGIN
IF(DATEPART(DW,@daterec) IN (6,7))
BEGIN
set @calcdatetime = @daterec + '23:59:00.000'
set @minutes = DATEDIFF(mi,@datetimerec,@calcdatetime)
UPDATE ##TempSOSLA
set ##TempSOSLA.MinsExcludeWeekend = ##TempSOSLA.MinsExcludeWeekend + @minutes
END
IF(DATEPART(DW,@daterec) IN (1))
BEGIN
set @calcdatetime = @daterec + '07:00:00.000'
set @minutes = DATEDIFF(MI,@datetimerec,@calcdatetime)
IF(@minutes >= 0)
BEGIN
UPDATE ##TempSOSLA
set ##TempSOSLA.MinsExcludeWeekend = ##TempSOSLA.MinsExcludeWeekend + @minutes
END
END
IF(DATEPART(DW,@daterec) IN (5))
BEGIN
set @calcdatetime = @daterec + '19:00:00.000'
set @minutes = DATEDIFF(MI,@datetimerec,@calcdatetime)
IF(@minutes >= 0)
BEGIN
UPDATE ##TempSOSLA
set ##TempSOSLA.MinsExcludeWeekend = ##TempSOSLA.MinsExcludeWeekend + @minutes
END
END
END

IF(@firstday<>1 and (@daterec<>@dateopen) and DATEPART(DW,@daterec) IN (1,5,6,7))
BEGIN
IF(DATEPART(DW,@daterec) IN (1))
BEGIN
Update ##TempSOSLA
set ##TempSOSLA.MinsExcludeWeekend = ##TempSOSLA.MinsExcludeWeekend + 420
END
IF(DATEPART(DW,@daterec) IN (5))
BEGIN
Update ##TempSOSLA
set ##TempSOSLA.MinsExcludeWeekend = ##TempSOSLA.MinsExcludeWeekend + 300
END
IF(DATEPART(DW,@daterec) IN (6,7))
BEGIN
Update ##TempSOSLA
set ##TempSOSLA.MinsExcludeWeekend = ##TempSOSLA.MinsExcludeWeekend + 1440
END
END

IF(@daterec=@dateopen and DATEPART(DW,@daterec) IN (1,5,6,7))
BEGIN
IF(DATEPART(DW,@daterec) IN (6,7))
BEGIN
set @calcdatetime = @dateopen + '23:59:00.000'
set @minutes = DATEDIFF(mi,@datetimeopen,@calcdatetime)
UPDATE ##TempSOSLA
set ##TempSOSLA.MinsExcludeWeekend = ##TempSOSLA.MinsExcludeWeekend + @minutes
END
IF(DATEPART(DW,@daterec) IN (1))
BEGIN
set @calcdatetime = @dateopen + '23:59:00.000'
set @minutes = DATEDIFF(mi,@datetimeopen,@calcdatetime)
IF(@minutes >= 0)
BEGIN
UPDATE ##TempSOSLA
set ##TempSOSLA.MinsExcludeWeekend = ##TempSOSLA.MinsExcludeWeekend + @minutes
END
END
IF(DATEPART(DW,@daterec) IN (5))
BEGIN
set @calcdatetime = @dateopen + '23:59:00.000'
set @minutes = DATEDIFF(mi,@datetimeopen,@calcdatetime)
IF(@minutes >= 0)
BEGIN
UPDATE ##TempSOSLA
set ##TempSOSLA.MinsExcludeWeekend = ##TempSOSLA.MinsExcludeWeekend + @minutes
END
END
END
END

ELSE
BEGIN
UPDATE ##TempSOSLA
set ##TempSOSLA.MinsExcludeWeekend="Yes"
END

set @firstday = @firstday + 1
set @minutes=0
set @daterec = DATEADD(dd,1,@daterec)
set @datetimerec = DATEADD(dd,1,@datetimerec)
END
set @Index = @Index + 1
END

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 12:42:23

Ugh!
We'll start here
Select @daterec = ##TempSOSLA.DateReceived
,@dateopen = ##TempSOSLA.DateOpened
, @datetimerec = ##TempSOSLA.DateReceived + ##TempSOSLA.TimeReceived
, @datetimeopen = ##TempSOSLA.DateOpened + ##TempSOSLA.TimeOpened
, @priority=##TempSOSLA.Priority

FROM ##TempSOSLA
Where RowId = @index

Jim

Everyday I learn something that somebody else already knew

creed65
Starting Member

2 Posts

Posted - 2011-05-11 : 14:06:50

Wow,

Why did that work thank you so much. I know this is really sloppy but I only have till the close of business today to get this done plus 8 other reports.

Thank You Again

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 14:17:28

This
BEGIN
SELECT * FROM ##TempSOSLA WHERE RowID = @Index

does absolutley nothing. That looks like a hangover from cursor-based programming. In SQL we select elements from datasets (values from tables)

Select @var1 = column1,@var2 = column2 from table is just how to set many variables at once from values in a table.

Now get back to work!

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources