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 |
nmm
Starting Member
3 Posts |
Posted - 2012-12-27 : 15:34:02
|
I have a query in 2005 and 2008 which should give the start and completion time of a job for last 6 months. However, if it crosses midnight the dates shown are bad..SELECT CONVERT(CHAR(10), start_time, 126) AS "DATE", host_name, job_name, job_class, operation, status, MIN(start_time) AS "START_TIME", MAX(completion_time) AS "COMPLETION_TIME", DATEDIFF(MINUTE, MIN(start_time), MAX(completion_time)) AS "DURATION" FROM dasdb..batch_job_log WHERE step_name IS NULL AND job_class = 'HOUSEKEEPING' AND status = 'SUCCEEDED' AND start_time IS NOT NULL AND completion_time IS NOT NULL GROUP BY CONVERT(CHAR(10), start_time, 126), host_name, job_name, job_class, operation, status ORDER BY CONVERT(CHAR(10), start_time, 126) DESCgoIn houskeeping we have multiple steps with start/end time. However, i want just the start time (eg 11pm) and end time (eg 4am).Please help me with the SQL. |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-27 : 17:21:40
|
Actually solution is possible until this statement :However, i want just the start time (eg 11pm) and end time (eg 4am).Read this:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
nmm
Starting Member
3 Posts |
Posted - 2012-12-28 : 09:04:06
|
Here is the Table Column's -Login_id Log_date Job_name Step_name Job_Class Operation Status Start_Time Completion_time OS_Login-------- ------------------------ --------- ------------- ------------ --------- --------- ------------------------- ------------------------- --------------24 7/5/2012 11:53:22.647 PM housekeep [NULL] HOUSEKEEPING RUN FAILED 7/5/2012 11:53:22.647 PM 7/5/2012 11:53:26.267 PM reavssqladm_pr 25 7/5/2012 11:53:23.063 PM housekeep gen_dbcc HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:53:23.063 PM 7/5/2012 11:53:23.757 PM reavssqladm_pr 26 7/5/2012 11:53:23.930 PM housekeep gen_updt_stat HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:53:23.930 PM 7/5/2012 11:53:24.397 PM reavssqladm_pr 27 7/5/2012 11:53:24.570 PM housekeep gen_recompile HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:53:24.570 PM 7/5/2012 11:53:25.000 PM reavssqladm_pr 28 7/5/2012 11:53:25.177 PM housekeep gen_dump HOUSEKEEPING RUN FAILED 7/5/2012 11:53:25.177 PM 7/5/2012 11:53:26.190 PM reavssqladm_pr 29 7/5/2012 11:54:01.383 PM housekeep [NULL] HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:54:01.383 PM 7/5/2012 11:54:03.050 PM reavssqladm_pr 30 7/5/2012 11:54:01.833 PM housekeep gen_dbcc HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:54:01.833 PM 7/5/2012 11:54:02.430 PM reavssqladm_pr 37 7/5/2012 11:54:12.717 PM housekeep [NULL] HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:54:12.717 PM 7/5/2012 11:54:16.150 PM reavssqladm_pr 38 7/5/2012 11:54:13.127 PM housekeep gen_dbcc HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:54:13.127 PM 7/5/2012 11:54:13.717 PM reavssqladm_pr 39 7/5/2012 11:54:13.913 PM housekeep gen_updt_stat HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:54:13.913 PM 7/5/2012 11:54:14.320 PM reavssqladm_pr 40 7/5/2012 11:54:15.077 PM housekeep gen_dump HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:54:15.077 PM 7/5/2012 11:54:15.800 PM reavssqladm_pr |
|
|
nmm
Starting Member
3 Posts |
Posted - 2012-12-28 : 09:05:21
|
Table DDL:USE dasdbgoCREATE TABLE dbo.batch_job_log( LOG_ID bigint NOT NULL, LOG_DATE datetime CONSTRAINT DF__batch_job__LOG_D__0CBAE877 DEFAULT getdate() NULL, HOST_NAME varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, DB_TYPE varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, INSTANCE_NAME varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, DB_NAME varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, JOB_NAME varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, STEP_NAME varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, JOB_CLASS varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, OPERATION varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, STATUS varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, START_TIME datetime NULL, COMPLETION_TIME datetime NULL, RUN_HOST varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, OS_LOGIN varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL, CONSTRAINT batch_job_log_pk PRIMARY KEY CLUSTERED (LOG_ID))goIF OBJECT_ID(N'dbo.batch_job_log') IS NOT NULL PRINT N'<<< CREATED TABLE dbo.batch_job_log >>>'ELSE PRINT N'<<< FAILED CREATING TABLE dbo.batch_job_log >>>'go |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-28 : 09:16:56
|
What is expected output and initial post is still not clear |
|
|
|
|
|
|
|