January 6, 2013 at 4:19 am
I'm Using the below script to get the results set accordingly.
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
StartTime EndTime BatchEndTime runtime
2012-10-09 03:05:00.720 2012-10-09 03:24:40.350 0 Hrs : 19 Mins
2012-12-20 19:15:01.960 2012-12-20 19:42:13.400 0 Hrs : 27 Mins
2012-06-18 12:55:52.810 2012-06-18 13:10:17.343 0 Hrs : 14 Mins
2012-12-06 20:20:00.300 2012-12-06 20:41:24.690 0 Hrs : 21 Mins
2012-07-01 02:55:22.490 2012-07-01 03:06:25.577 0 Hrs : 11 Mins
2012-11-26 19:36:32.530 2012-11-26 20:01:51.237 0 Hrs : 25 Mins
2012-03-26 02:55:30.077 2012-03-26 03:27:33.907 0 Hrs : 32 Mins
2012-08-27 03:01:10.240 2012-08-27 03:15:31.030 0 Hrs : 14 Mins
2012-09-22 03:10:01.060 2012-09-22 03:25:44.690 0 Hrs : 15 Mins
2012-08-15 03:04:51.900 2012-08-15 10:50:45.740 7 Hrs : 45 Mins
2012-04-19 02:45:17.747 2012-04-19 02:55:29.613 0 Hrs : 10 Mins
2012-11-22 04:15:01.140 2012-11-22 04:41:07.500 0 Hrs : 26 Mins
2012-12-25 19:15:02.533 2012-12-25 19:40:04.200 0 Hrs : 25 Mins
Now 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 help
January 6, 2013 at 4:39 am
Not WHERE clause, but HAVING, because you want filter out groups. Add after GROUP BY block HAVING Max(WQ.WorkQueueEndWorkDate))%3600/60)>=120
January 6, 2013 at 4:46 am
I did something similar.. thanks a lotwhere cast((datediff (ss,BatchStartTime,BatchEndTime)) as decimal(8,0))/60.0 > 120
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply