Need to find the Duration - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need to find the Duration

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-09-14 : 13:15:51

Hi All
I need to find out the duration for last 30 days for a my job .I have built the query which has start date time and end datetime of job ..but here I am not able to retrieve the duration of the job for particular date. I want to minus time from the endtime to starttime and want to retrieve the duration and want to dispaly the data like
startdate enddate duration

Please find my query where i retrieve the records for last two days .
But not able to implement it on above mentioned colums.. as it is production server and I dont have rights to create any table ..only have read permission ... thts why i am finding it very difficult myself ...please advise ..

here is my query and resulted output

SELECT * FROM SSIS..LogDetail (Nolock) WHERE
( jobName like '%EnterpriseJPFactEnroll%' or Source like '%EnterpriseJPFactEnroll%') and MessageID in
('JEDI-0227','JEDI-0228')and
LogDateTime BETWEEN DATEADD(DD,-2,CAST(CONVERT(VARCHAR(15),GETDATE(),106) AS DATETIME))
AND
CAST(CONVERT(VARCHAR(15),GETDATE(),106) AS DATETIME)
order by LogDateTime

output:

LogID LogDateTime Source JobName MessageID Severity Title Description BuildValue Alerted
1315095 2011-09-13 06:46:58.840 EnterpriseJPFactEnroll NULL JEDI-0227 INFO Begin Processing Enroll Measure Group NULL NULL 1
1315400 2011-09-13 07:33:36.227 EnterpriseJPFactEnroll NULL JEDI-0228 INFO End Processing Enroll Measure Group NULL NULL 1
1322692 2011-09-14 06:51:30.037 EnterpriseJPFactEnroll NULL JEDI-0227 INFO Begin Processing Enroll Measure Group NULL NULL 1
1322993 2011-09-14 07:36:48.270 EnterpriseJPFactEnroll NULL JEDI-0228 INFO End Processing Enroll Measure Group NULL NULL 1

I hope I am clear with my requirement..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 13:29:23

does each of above represents the various steps of job? if not, i cant see a end time so didnt understand how you would determine when job would end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-09-14 : 13:42:17

Hi Thanks for your observation

LogDateTime colum is having a start datetime and end time for the particular date .If you look at the LogDateTime which contains the start date and end date of the job ..

2011-09-13 06:46:58.840 -- its start dateand time
2011-09-13 07:33:36.227 -- its end date and time for a job on given day ..

Likewise this job runs everyday and LogDateTime column contais the start date and endtime for a job

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 01:10:40

but how do you determine which end time corresponds to which start time for a job? is there not an execution id which indicates which execution the start and end times are a part of. What if a job runs multiple times on a day probably due to failure. in that case there will be difficulty in finding out start and end times for each run

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-09-15 : 14:11:52

I got the resulted output as I can create a Temp Table

create table #temp( rundate datetime,duration int)
declare @i int,@duration int,@rundate datetime
set @i = 0
while ( @i < 30)
begin

SELECT @Rundate = cast(convert(varchar(20),a.LOGDATETIME ,101) as date ),
@duration = DATEDIFF(minute,(SELECT MAX(LOGDATETIME) FROM SSIS..LOGDETAIL (NOLOCK)
WHERE DATEDIFF(DD,LOGDATETIME,GETDATE()) = @i and MessageID = 'JEDI-0227') ,(SELECT MAX(LOGDATETIME) FROM SSIS..LOGDETAIL (NOLOCK)
WHERE DATEDIFF(DD,LOGDATETIME,GETDATE()) = @i and MessageID = 'JEDI-0228'))
from SSIS..LOGDETAIL (NOLOCK) a where cast(convert(varchar(20),a.LOGDATETIME ,101) as date )
= cast(convert(varchar(20),GETDATE()-@i ,101) as date )

insert into #temp(rundate,duration)values(@Rundate,@duration)
set @i = @i + 1
end
select LEFT(rundate,11)as rundate,duration from #temp order by rundate desc
drop table #temp

This query can find out the duration time for my requrement:

Thanks
VIjay Sahu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 14:22:25

may be it serves your requirement. But my questions still stand!
You've not given any explanation for them yet

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources