merge query - 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

merge query

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-06-01 : 07:12:46

how i merge select and update query

first i want to select data and then i want to update that data

this is my select query

SELECT CalDate,[Timein],[Timeout],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108) AS SpendTime
FROM attendance

this is my update query

update attendance set
excessshort =
CAST ( ABS(600 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +
CAST ( ABS(600 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )
from
attendance

how i merge that query

immad uddin ahmed

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-01 : 09:19:39

quote:
Originally posted by immad

how i merge select and update query

first i want to select data and then i want to update that data

this is my select query

SELECT CalDate,[Timein],[Timeout],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108) AS SpendTime
FROM attendance

this is my update query

update attendance set
excessshort =
CAST ( ABS(600 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +
CAST ( ABS(600 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )
from
attendance

how i merge that query

immad uddin ahmed

You can use OUTPUT clause as shown below if you are on SQL 2005 or later

update attendance set
excessshort = 
CAST ( ABS(600 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +	
CAST ( ABS(600 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )
output INSERTED.CalDate,INSERTED.[Timein],INSERTED.[Timeout],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,INSERTED.[Timein],INSERTED.[Timeout]),0),108)

BTW, you can simply subtract one time from the other to do the conversion -

CONVERT(varchar(8),INSERTED.[Timein]-INSERTED.[Timeout],108)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-03 : 03:15:53

>> first i want to select data and then i want to update that data

I think you need old data before UPDATE....
So you can use DELETED virtual table to display OLD DATA before UPDATE

update attendance set
excessshort =
CAST ( ABS(600 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +
CAST ( ABS(600 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )
output DELETED.CalDate, DELETED.[Timein],DELETED.[Timeout],
CONVERT(varchar(8),DELETED.[Timein]-DELETED.[Timeout],108)

--
Chandu

immad
Posting Yak Master

230 Posts

Posted - 2013-06-03 : 03:36:44

Thank for the help

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 03:53:33

whats the datatype of excessshort?

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-04 : 00:42:32

quote:
Originally posted by immad

Thank for the help

immad uddin ahmed

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources