InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 15:46:07 GMT20http://www.sqlservercentral.com/Forums/Topic1403321-392-1.aspxI did something similar.. thanks a lot[code="sql"]where cast((datediff (ss,BatchStartTime,BatchEndTime)) as decimal(8,0))/60.0 > 120[/code]Sun, 06 Jan 2013 04:46:47 GMTMac1986http://www.sqlservercentral.com/Forums/Topic1403321-392-1.aspxNot WHERE clause, but HAVING, because you want filter out groups. Add after GROUP BY block [code="sql"]HAVING Max(WQ.WorkQueueEndWorkDate))%3600/60)>=120[/code]Sun, 06 Jan 2013 04:39:20 GMTe4d4http://www.sqlservercentral.com/Forums/Topic1403321-392-1.aspxI'm Using the below script to get the results set accordingly. [code="sql"]SELECT Min(wq.WorkQueueStartWorkDate) BatchStartTime, max(wq.WorkQueueEndWorkDate) BatchEndTime, convert(varchar(5),DateDiff(s, min(WQ.WorkQueueStartWorkDate),Max(WQ.WorkQueueEndWorkDate))/3600)+ ' Hrs' +' : '+ convert(varchar(5),DateDiff(s, min(WQ.WorkQueueStartWorkDate),Max(WQ.WorkQueueEndWorkDate))%3600/60) + ' Mins' as 'Runtime'FROM dbVelocityMetadata..FileArrival fa (nolock)JOIN dbVelocityMetadata..FileArrivalGroup fag (nolock) ON fag.FileArrivalGroupID=fa.FileArrivalGroupID JOIN dbVelocityMetadata.dbo.CDCDataExtractor_LoadFile LF (nolock) ON LF.FileArrivalID=fa.FileArrivalID JOIN dbVelocityMetadata..LoadFileRun LFR (nolock) ON LFR.LoadFileID=LF.LoadFileID JOIN dbVelocityMetadata..WorkQueue wq (nolock) ON wq.WorkQueueID=LFR.WorkQueueID where fag.FileSourceDataSetInstanceName='C2C01' --and fag.FileSourceExecutionKey > (select max(cast(BatchNumber as bigint)) from Operations..DatasetSLASummary nolock where datasetname = 'Velocity_RMCA')group by fag.filesourcedatasetinstancename,fag.FileSourceExecutionKey [/code]StartTime EndTime BatchEndTime runtime 2012-10-09 03:05:00.720 2012-10-09 03:24:40.350 0 Hrs : 19 Mins2012-12-20 19:15:01.960 2012-12-20 19:42:13.400 0 Hrs : 27 Mins2012-06-18 12:55:52.810 2012-06-18 13:10:17.343 0 Hrs : 14 Mins2012-12-06 20:20:00.300 2012-12-06 20:41:24.690 0 Hrs : 21 Mins2012-07-01 02:55:22.490 2012-07-01 03:06:25.577 0 Hrs : 11 Mins2012-11-26 19:36:32.530 2012-11-26 20:01:51.237 0 Hrs : 25 Mins2012-03-26 02:55:30.077 2012-03-26 03:27:33.907 0 Hrs : 32 Mins2012-08-27 03:01:10.240 2012-08-27 03:15:31.030 0 Hrs : 14 Mins2012-09-22 03:10:01.060 2012-09-22 03:25:44.690 0 Hrs : 15 Mins2012-08-15 03:04:51.900 2012-08-15 10:50:45.740 7 Hrs : 45 Mins2012-04-19 02:45:17.747 2012-04-19 02:55:29.613 0 Hrs : 10 Mins2012-11-22 04:15:01.140 2012-11-22 04:41:07.500 0 Hrs : 26 Mins2012-12-25 19:15:02.533 2012-12-25 19:40:04.200 0 Hrs : 25 MinsNow I need to out a where clause to filterout all the records which are >= 2 Hrs.How can I do it. Because I'm getting an error if I try the same because the column Runime is Varchar. I need to at the urgency.. Please helpSun, 06 Jan 2013 04:19:17 GMTMac1986