0

I'm trying to assign a calculation involving two table columns as the value of a variable. I keep getting the error that the multi-part identifier could not be bound. My ultimate objective is to recreate the variable in a stored procedure because I would like to use that variable in other calculations in the same stored procedure.

My query is below.

USE dbAttendanceHR
GO

DECLARE @WorkDuration AS DECIMAL

SET @WorkDuration = CAST(DATEDIFF(minute, dbo.tblAttendance.ClockInTime, dbo.tblAttendance.ClockOutTime) AS FLOAT) / 60

SELECT @WorkDuration

The error message are...

Msg 4104, Level 16, State 1, Line 5 The multi-part identifier "dbo.tblAttendance.ClockInTime" could not be bound.

Msg 4104, Level 16, State 1, Line 5 The multi-part identifier "dbo.tblAttendance.ClockOutTime" could not be bound.

Please help. Thanks.

2
  • 3
    To access columns in a table, you must SELECT the rows that contain them using a SELECT statement. And since we can expect a table to have many rows, what value do you expect from your calculation - which appears to assume a single row? Did you intend to calcuculate this value for every row in the table?
    – SMor
    Mar 15, 2022 at 11:51
  • Hello SMor. Yes I intend to calculate this value for every row that is returned by a query. Mar 16, 2022 at 9:17

2 Answers 2

0

Does this fit your requests?

USE dbAttendanceHR
GO 
        
DECLARE @WorkDuration AS NVARCHAR(max)
        
SET @WorkDuration = 'SELECT ' + 'CAST(DATEDIFF(minute, dbo.tblAttendance.ClockInTime, dbo.tblAttendance.ClockOutTime) AS FLOAT) / 60'
        
EXECUTE sp_executesql @WorkDuration
2
  • Hi Francesco. Thanks for your response. The variable is supposed to be a decimal type. Is there a reason why you changed it to text? Recall that I intend to use this variable in other calculations in the same stored procedure. You code literally sets the value of the variable to: SELECT ' + 'CAST(DATEDIFF(minute, dbo.tblAttendance.ClockInTime, dbo.tblAttendance.ClockOutTime) AS FLOAT) / 60 Mar 16, 2022 at 9:13
  • Yes, what data type are you using for ClockOutTime ? I'm going to test it with some date on AdventureWork so we can be on the same page. Mar 16, 2022 at 13:54
0

You can not do calculations based on columns, instead you have to do the calculations with the value in a cell. The way you try accomplishing your goal is to tell the SQL Server to "take the diffrerence in minutes between all values in column ClockInTime and column ClockOutTime."

SQL Server can not process that command since it is not syntactically correct. You have to go trough all the rows in table tblAttendance, calculate the difference between the values in ClockInTime and ClockOutTime so you get a WorkDuration per row in the table.

A possible solution yould be to use as cursor and iterate over all the rows in the table, do the calculation and use the result of that calculation.

DECLARE @ClockInTime  DATETIME,
        @ClockOutTime DATETIME,
        @WorkDuration FLOAT;

DECLARE cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
SELECT ClockInTime,
       ClockOutTime
FROM   dbo.tblAttendance;

OPEN cur;

FETCH NEXT FROM cur
INTO @ClockInTime,
     @ClockOutTime;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @WorkDuration = CAST(DATEDIFF(MINUTE, @ClockInTime, @ClockOutTime) AS FLOAT) / 60;

    SELECT @WorkDuration;

    FETCH NEXT FROM cur
    INTO @ClockInTime,
         @ClockOutTime;
END;

CLOSE cur;
DEALLOCATE cur;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.