How update Select Column - 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

How update Select Column

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-06-09 : 10:28:43

this is my select query

select
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 red just like spend and excessshort

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 )--
,--
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108)
from
#attendance

please suggest me a syntax
Thank you for the help

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-09 : 11:49:58

please post full query. i can see t4. but cant see table referred by t4 so dont know how exactly you want to link

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-06-09 : 11:58:23

i am sory t4.minute is 540 let me change it

select
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, [Timein], [Timeout]) >= 540 and [Timein] = [Timein] and [Timeout] = [Timeout] THEN 'Excess' else 'Short'
END as Excess,
from
#attendance

i want to update excess column written in red just like spend and excessshort

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 )--
,--
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108)
from
#attendance

please suggest me a syntax
Thank you for the help

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-09 : 12:08:31

[code]
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 )--
,--
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108),
Excess = CASE WHEN DATEDIFF(MINUTE, [Timein], [Timeout]) >= 540 and [Timein] = [Timein] and [Timeout] = [Timeout] THEN 'Excess' else 'Short'
END
from
#attendance
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-06-10 : 00:55:30

Thanks

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-10 : 07:56:59

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-06-11 : 01:46:49

i have a small problem regarding this topic

ALTER procedure [dbo].[query]
(
@empid nvarchar(50)

)
as
begin
select
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, [Timein], [Timeout]) >= 540 and [Timein] = [Timein] and [Timeout] = [Timeout] THEN 'Excess' else 'Short'
END as Excess,
from
attend_log
where eid=@empid

update
attend_log
set
excessshort =
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )
,
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108),

Excess = CASE WHEN DATEDIFF(MINUTE, [Timein], [Timeout]) >= 540 and [Timein] = [Timein] and [Timeout] = [Timeout] THEN 'Excess' else 'Short'
END
from
where eid=@empid
attend_log
end

when i exec this procedure first time it give that result

date----------------------------EID-------timein-----------------------------timeout-----------------spendtime------excessshort
2013-04-11 00:00:00.000---26492-----2013-06-10 13:29:00.000--2013-06-10 15:44:00.000---02:15:00-------NULL

then again i execute procedure its give me this result

date----------------------------EID-------timein-----------------------------timeout-----------------spendtime------excessshort
2013-04-11 00:00:00.000---26492-----2013-06-10 13:29:00.000--2013-06-10 15:44:00.000---02:15:00-----6 hrs : 45 min

may be its an end problem written in red

immad uddin ahmed

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-11 : 02:03:44

2nd time you will obviously get the updated value (caused by first time execution of SP)

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 02:04:43

hmm...thats what is expected..
First time the select returns values before update so that may be why excess short was NULL. After that update will happen and next time when you run the procedure select will give you the updated result which is why you see the value there.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-06-11 : 02:13:00

quote:
Originally posted by visakh16

hmm...thats what is expected..
First time the select returns values before update so that may be why excess short was NULL. After that update will happen and next time when you run the procedure select will give you the updated result which is why you see the value there.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

any suggestion

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 02:15:30

quote:
Originally posted by immad

quote:
Originally posted by visakh16

hmm...thats what is expected..
First time the select returns values before update so that may be why excess short was NULL. After that update will happen and next time when you run the procedure select will give you the updated result which is why you see the value there.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

any suggestion

immad uddin ahmed

FOr what?
Its already working the expected way so didnt understand what you expect to change

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-11 : 02:17:01

quote:
Originally posted by immad

quote:
Originally posted by visakh16

hmm...thats what is expected..
First time the select returns values before update so that may be why excess short was NULL. After that update will happen and next time when you run the procedure select will give you the updated result which is why you see the value there.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

any suggestion

immad uddin ahmed

You got correct result.. whats the problem there?
If you want updated value for first time means
Put SELECT query after UPDATE statement in SP

--
Chandu

immad
Posting Yak Master

230 Posts

Posted - 2013-06-11 : 05:18:19

well i fix the problem
now its select and update both in one execute

immad uddin ahmed

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-11 : 05:19:52

quote:
Originally posted by immad

well i fix the problem
now its select and update both in one execute
immad uddin ahmed

ok...
>> now its select and update both in one execute
means have you used OUTPUT option along with UPDATE Statement?

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 05:22:03

quote:
Originally posted by immad

well i fix the problem
now its select and update both in one execute

immad uddin ahmed

what problem?
there was no problem in first place other than order being reversed if you intention was to show the updated result ie select after update

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources