Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help in putting the correct WHERE Clause Expand / Collapse
Author
Message
Posted Sunday, January 6, 2013 4:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:27 PM
Points: 219, Visits: 688
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
Post #1403321
Posted Sunday, January 6, 2013 4:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:47 AM
Points: 223, Visits: 1,718
Not WHERE clause, but HAVING, because you want filter out groups. Add after GROUP BY block
HAVING Max(WQ.WorkQueueEndWorkDate))%3600/60)>=120

Post #1403323
Posted Sunday, January 6, 2013 4:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:27 PM
Points: 219, Visits: 688
I did something similar.. thanks a lot
where cast((datediff (ss,BatchStartTime,BatchEndTime)) as decimal(8,0))/60.0 > 120

Post #1403325
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse