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.
Author |
Topic |
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-09 : 10:28:43
|
this is my select queryselectCalDate,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#attendancei want to update excess column written in red just like spend and excessshortupdate#attendancesetexcessshort =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#attendanceplease suggest me a syntax Thank you for the helpimmad 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 MVPhttp://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 selectCalDate,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#attendancei want to update excess column written in red just like spend and excessshortupdate#attendancesetexcessshort =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#attendanceplease suggest me a syntax Thank you for the helpimmad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-09 : 12:08:31
|
[code]update#attendancesetexcessshort =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'ENDfrom#attendance[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-10 : 00:55:30
|
Thanksimmad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-10 : 07:56:59
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 topicALTER procedure [dbo].[query](@empid nvarchar(50))asbeginselectCalDate,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,fromattend_logwhere eid=@empidupdateattend_logsetexcessshort =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'ENDfromwhere eid=@empidattend_logendwhen i exec this procedure first time it give that resultdate----------------------------EID-------timein-----------------------------timeout-----------------spendtime------excessshort2013-04-11 00:00:00.000---26492-----2013-06-10 13:29:00.000--2013-06-10 15:44:00.000---02:15:00-------NULLthen again i execute procedure its give me this resultdate----------------------------EID-------timein-----------------------------timeout-----------------spendtime------excessshort2013-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 minmay be its an end problem written in redimmad 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 MVPhttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
any suggestionimmad 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
any suggestionimmad uddin ahmed
FOr what?Its already working the expected way so didnt understand what you expect to change------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
any suggestionimmad uddin ahmed
You got correct result.. whats the problem there?If you want updated value for first time meansPut 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 problemnow its select and update both in one executeimmad 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 problemnow its select and update both in one executeimmad uddin ahmed
ok...>> now its select and update both in one executemeans 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 problemnow its select and update both in one executeimmad 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|