immaduddinahmed (6/10/2013)
this is my select queryselect
CalDate,
Timein,
Timeout ,
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108) AS SpendTime_runtime,
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar ) as excesstime_runtime,
CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= t4.minute and t.[Timein] = t.[Timein] and t.[Timeout] = t.[Timeout] THEN 'Excess' else 'Short'
END as Excess,
from
#attendance
i want to update excess column written in select query
update
#attendance
set
excessshort =
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )-- Minutes
,-- Only here so visible
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108)
from
#attendance
please suggest me a syntax
Thank you for the help
If I understand you correctly, you want to update the [Excess] column.
To make it easy to read, you could declare a varchar variable. Then update it with the value you need. Then include it in your update statement. So something like:
DECLARE @excess varchar(6)
SET @excess = Select 'Then your CASE statement here
update
#attendance
set
excessshort =
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )-- Minutes
,-- Only here so visible
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108),
Excess = @excess
from
#attendance