Updating Pend Time - 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

General SQL Server Forums

New to SQL Server Programming

Updating Pend Time

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-29 : 13:15:25

Hi ,

I have some complicated data, for which i need to calculate the metrics such as PendTime ,
My sample data Looks as something as in below link ,

http://download.yousendit.com/MVNmMWZBQ3RmVFpMWEE9PQ

The logic for calculating the pendtime is (starttime-runnabletime )where the ec_step_index is 0 and min (runnable_time) among all the steps for a job ...

I was trying something like below, but i get error for using group by ...

CREATE PROCEDURE PROC_UPDATE_PEND_TIME

AS
BEGIN
update dbo.DW_T_ASW_Report_Table set PENDING_TIME_SEC = (DATEDIFF(SECOND,EC_RUNNABLE_TIME,EC_start_time))
where EC_STEP_INDEX is 0 and EC_RUNNABLE_TIME= MIN(EC_RUNNABLE_time)
GROUP BY JOB_ID
END
GO

A job can have any # steps or parellel steps running , so could any one let me know the query for writing this in a stored procedure, so that i could run that procedure whenever i insert or update the data ...

Please look into the excel file for my sample data(click on above link)..

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-29 : 13:51:39

quote:

so that i could run that procedure whenever i insert or update the data ...

That would be a trigger...

You also need to post

DDL of the Table
Sample Data
A DML Operation
What the data would look like AFTER that operation

Do that, answer in 2 minutes...really

Read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-29 : 14:17:07

i HAVE THE SAMPLE DATA IN AN EXCEL FILE , WHERE I UPLOADED TO YOUSENDIT.COM , HERE IS THE LINK

http://download.yousendit.com/MVNmMWZBQ3RmVFpMWEE9PQ

THE EXPECTED OUTPUT OF THE PEND TIME IS MARKED IN PINK COLOUM...

STRUCTURE OF THE TABLE :
CREATE TABLE [dbo].[DW_T_ASW_Report_Table](
[JOB_ID] [bigint] NULL,
[PENDING_TIME] [int] NULL,
[EC_RUNNABLE_TIME] [datetime] NULL,
[START_TIME] [datetime] NULL,
[EC_JOB_STEP_NAME] [varchar](255) NOT NULL,
[EC_STEP_INDEX] [int] NULL
) ON [PRIMARY]

GO

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-29 : 14:28:53

Yes but it's a blocked site for some of us...(me)

CASP DONT HELP

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-29 : 14:43:09

My data looks bit complicated if i just paste it here , is there any other way where i can send my excel file?

Please help...

EC_JOB_ID EC_RUNNABLE_TIME EC_START_TIME EC_JOB_STEP_NAME EC_STEP_INDEX Pend_Time_SEC
101408 5/1/2009 8:04:43 5/1/2009 8:04:43 Show Lint Summary 0 0
101408 5/1/2009 8:04:48 5/1/2009 8:04:48 Print Lint Report 1 0
101408 5/1/2009 8:04:51 5/1/2009 8:05:19 Remove build 0 28
101408 5/1/2009 8:05:23 5/1/2009 8:05:23 EmailNotifier 0 0
101408 5/1/2009 8:02:40 5/1/2009 8:02:41 Setup changelist Link 0 1
101408 5/1/2009 8:02:43 5/1/2009 8:02:43 rmTmpChangeListHtmlFile 1 0
101408 5/1/2009 8:03:21 5/1/2009 8:03:21 Removing the dynamic Clientspec 3 0
101408 5/1/2009 8:03:25 5/1/2009 8:03:25 build 1 0
101408 5/1/2009 8:04:43 5/1/2009 8:04:43 Metrics Summary 2 0
101408 5/1/2009 8:04:51 5/1/2009 8:05:19 Removing a Build 3 28
101408 5/1/2009 8:05:23 5/1/2009 8:05:23 GenerateEmaiNotifier 4 0
101408 5/1/2009 8:00:06 5/1/2009 8:00:09 Generating dynamic Clientspec & check for changes 0 3
101408 5/1/2009 8:02:40 5/1/2009 8:02:41 Setup Links 1 1
101408 5/1/2009 8:02:46 5/1/2009 8:02:46 Sync to Perforce 2 0
101408 5/1/2009 8:00:05 5/1/2009 8:00:09 Check for whether we need a build 0 4
101408 5/1/2009 8:00:04 5/1/2009 8:00:03 job_101031541800568017779-root NULL -1
840789 8/12/2009 20:04:43 8/12/2009 20:04:43 Setup changelist Link 0 0
840789 NULL 8/12/2009 20:05:07 Show Lint Summary 0 NULL
840789 NULL 8/12/2009 20:05:07 Print Lint Report 1 NULL
840789 NULL 8/12/2009 20:05:07 Remove build 0 NULL
840789 NULL 8/12/2009 20:05:07 EmailNotifier 0 NULL
840789 8/12/2009 20:01:01 8/12/2009 20:01:00 job_208799084901487356749-root NULL -1
840789 8/12/2009 20:01:01 8/12/2009 20:03:32 Check for whether we need a build 0 151
840789 NULL 8/12/2009 20:05:07 build 1 NULL
840789 8/12/2009 20:05:07 8/12/2009 20:05:07 Metrics Summary 2 0
840789 8/12/2009 20:05:07 8/12/2009 20:05:07 Removing a Build 3 0
840789 8/12/2009 20:05:07 8/12/2009 20:05:07 GenerateEmaiNotifier 4 0
840789 8/12/2009 20:01:02 8/12/2009 20:03:32 Generating dynamic Clientspec & check for changes 0 150
840789 8/12/2009 20:04:43 8/12/2009 20:04:44 Setup Links 1 1
840789 NULL 8/12/2009 20:04:56 Sync to Perforce 2 NULL
840789 8/12/2009 20:04:56 8/12/2009 20:04:56 Removing the dynamic Clientspec 3 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-30 : 00:22:35

ok now what should be output out of sample data above?

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-30 : 18:37:05

i have calculated the pending time by doing starttime-runnabletime for the all job steps , basically 1 job has multiple job steps , i need to create a new table where pend time of each job is updated as the value i get by calculating starttime- ruunabletime , where the job step's stepindex is 0 , as there are multiple job steps with step index 0 , while calculating the pendtime of a job , i need to consider least runnnabletime among all the runnabletimes where step index is 0,

So, the expected ouput should be something like below,

EC_JOB_ID EC_RUNNABLE_TIME EC_START_TIME EC_JOB_STEP_NAME EC_STEP_INDEX Pend_Time_SEC
101408 5/1/2009 8:00:05 5/1/2009 8:00:09 Check for whether we need a build 0 4
840789 8/12/2009 20:01:01 8/12/2009 20:03:32 Check for whether we need a build 0 151

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-31 : 21:47:13

Please let me know if i can send the excel file of my data and expected output , to any email id if i can.....

-thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 01:27:37

Can i ask how you got output record as one with Pend_Time_SEC as 4 for job id 101408 when you've records with Pend_Time_SEC 0 for EC_STEP_INDEX 0 (which is least compared to 4)

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-01 : 12:26:32

From my data :

EC_JOB_ID EC_RUNNABLE_TIME EC_START_TIME EC_JOB_STEP_NAME EC_STEP_INDEX Pend_Time_SEC
101408 5/1/2009 8:04:43 5/1/2009 8:04:43 Show Lint Summary 0 0
101408 5/1/2009 8:04:48 5/1/2009 8:04:48 Print Lint Report 1 0
101408 5/1/2009 8:04:51 5/1/2009 8:05:19 Remove build 0 28
101408 5/1/2009 8:05:23 5/1/2009 8:05:23 EmailNotifier 0 0
101408 5/1/2009 8:02:40 5/1/2009 8:02:41 Setup changelist Link 0 1
101408 5/1/2009 8:02:43 5/1/2009 8:02:43 rmTmpChangeListHtmlFile 1 0
101408 5/1/2009 8:03:21 5/1/2009 8:03:21 Removing the dynamic Clientspec 3 0
101408 5/1/2009 8:03:25 5/1/2009 8:03:25 build 1 0
101408 5/1/2009 8:04:43 5/1/2009 8:04:43 Metrics Summary 2 0
101408 5/1/2009 8:04:51 5/1/2009 8:05:19 Removing a Build 3 28
101408 5/1/2009 8:05:23 5/1/2009 8:05:23 GenerateEmaiNotifier 4 0
101408 5/1/2009 8:00:06 5/1/2009 8:00:09 Generating dynamic Clientspec & check for changes 0 3
101408 5/1/2009 8:02:40 5/1/2009 8:02:41 Setup Links 1 1
101408 5/1/2009 8:02:46 5/1/2009 8:02:46 Sync to Perforce 2 0
101408 5/1/2009 8:00:05 5/1/2009 8:00:09 Check for whether we need a build 0 4
101408 5/1/2009 8:00:04 5/1/2009 8:00:03 job_101031541800568017779-root NULL -1
840789 8/12/2009 20:04:43 8/12/2009 20:04:43 Setup changelist Link 0 0
840789 NULL 8/12/2009 20:05:07 Show Lint Summary 0 NULL
840789 NULL 8/12/2009 20:05:07 Print Lint Report 1 NULL
840789 NULL 8/12/2009 20:05:07 Remove build 0 NULL
840789 NULL 8/12/2009 20:05:07 EmailNotifier 0 NULL
840789 8/12/2009 20:01:01 8/12/2009 20:01:00 job_208799084901487356749-root NULL -1
840789 8/12/2009 20:01:01 8/12/2009 20:03:32 Check for whether we need a build 0 151
840789 NULL 8/12/2009 20:05:07 build 1 NULL
840789 8/12/2009 20:05:07 8/12/2009 20:05:07 Metrics Summary 2 0
840789 8/12/2009 20:05:07 8/12/2009 20:05:07 Removing a Build 3 0
840789 8/12/2009 20:05:07 8/12/2009 20:05:07 GenerateEmaiNotifier 4 0
840789 8/12/2009 20:01:02 8/12/2009 20:03:32 Generating dynamic Clientspec & check for changes 0 150
840789 8/12/2009 20:04:43 8/12/2009 20:04:44 Setup Links 1 1
840789 NULL 8/12/2009 20:04:56 Sync to Perforce 2 NULL
840789 8/12/2009 20:04:56 8/12/2009 20:04:56 Removing the dynamic Clientspec 3 0

I have underlined the record from where i have calculated the pend_time_sec as 4 , as that has step_index 0 and its runnable time is the least runnabletime of all jobsteps with step_index 0 for the job 101408

i used (datediff(second,runnable_time,start_time))...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 12:28:25

what about ones like 101408 5/1/2009 8:05:23 5/1/2009 8:05:23 EmailNotifier 0 0 which has Pend_Time_SEC of 0?

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-01 : 13:24:32

Please look into the ruunnable time , i need to consider the least of runnabletimes for all job steps where step index is 0,

quote:
what about ones like 101408 5/1/2009 8:05:23 5/1/2009 8:05:23 EmailNotifier 0 0 which has Pend_Time_SEC of 0?

has the runnable time 8:05:23 which is greater than 8:00:05

Please Note 2 condition for calcuating the job pendtime (not job step)
1.Stepindex has to be 0
2.Least of all runnable times where step index is 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 13:33:56

then its just a matter of


SELECT t.*
FROM YourTable t
INNER JOIN (SELECT EC_JOB_ID,MIN(EC_RUNNABLE_TIME) AS Start
            FROM YourTable
            WHERE EC_STEP_INDEX=0
            GROUP BY EC_JOB_ID) t1
ON t1.EC_JOB_ID=t.EC_JOB_ID
AND t1.Start = t.EC_RUNNABLE_TIME

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-01 : 14:43:39

Can you please apply that logic in my insert statement here , i get an error if i do ...the NULL has to be updated with the logic i have for the pend time , i need to have this as a part of my insert statement.....Please let me know if its possible to do so , if not i need look for another option...

Insert INTO DW_T_ASW_REPORT_TABLE(

JOB_ID,
JOB_NAME,
JOB_TYPE,
PENDING_TIME_MIN,
QUEUE_TIME,
RUNTIME_MIN,
START_TIME,
EC_JOB_STEP_NAME,
EC_STEP_INDEX
)
(
SELECT

E.EC_JOB_ID,
E.EC_JOB_NAME,
E.EC_JOB_IND,
NULL,
E.EC_RUNNABLE_TIME,
DATEDIFF(MINUTE,E.EC_START_TIME,E.EC_FINISH_TIME),
E.EC_START_TIME,
E.EC_JOB_STEP_NAME,
E.EC_STEP_INDEX

FROM dbo.DW_T_EC_SBA E)

Thanks!

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-01 : 18:05:29

Could any one assist me in writing a stored procedure for updating the pend time i am trying using below , but i get error
please help...

CREATE PROCEDURE [dbo].[Proc_Update_PendTime]
AS

BEGIN

UPDATE dbo.DW_T_ASW_JOBDATA SET PENDING_TIME_MIN = (DATEDIFF(MINUTE ,QUEUE_TIME,START_TIME))WHERE JOB_STEP_INDEX = 0
AND RUNNABLE_TIME = MIN(RUNNABLE_TIME)
GROUP BY JOB_ID
END

quote:
Originally posted by rds207

Can you please apply that logic in my insert statement here , i get an error if i do ...the NULL has to be updated with the logic i have for the pend time , i need to have this as a part of my insert statement.....Please let me know if its possible to do so , if not i need look for another option...

Insert INTO DW_T_ASW_REPORT_TABLE(

JOB_ID,
JOB_NAME,
JOB_TYPE,
PENDING_TIME_MIN,
QUEUE_TIME,
RUNTIME_MIN,
START_TIME,
EC_JOB_STEP_NAME,
EC_STEP_INDEX
)
(
SELECT

E.EC_JOB_ID,
E.EC_JOB_NAME,
E.EC_JOB_IND,
NULL,
E.EC_RUNNABLE_TIME,
DATEDIFF(MINUTE,E.EC_START_TIME,E.EC_FINISH_TIME),
E.EC_START_TIME,
E.EC_JOB_STEP_NAME,
E.EC_STEP_INDEX

FROM dbo.DW_T_EC_SBA E)

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 04:06:37

you want to update all records pend time with minimum value?

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-02 : 15:50:11

no not the minumum values but a calculated value for pend for the job , which is starttime-ruunnabletime where the step index is o , as there are mutiple step indexes with o , while calculating starttime- runnabletime , runnable time has to be minumum of all runnble times where step index is o , and that value has to be update where stepindex is null....

quote:
Originally posted by visakh16

you want to update all records pend time with minimum value?

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-02 : 23:54:49

if i want the associated start_time , where step index is 0 and least of the runnable time ,

i tried adding ec_start_time for the given select statement , i get an error saying

"Column 'dbo.DW_T_EC_HCI_BREW.EC_START_TIME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause".

quote:
SELECT EC_JOB_ID,MIN(EC_RUNNABLE_TIME) AS runnable_time,
ec_start_time
FROM YourTable
WHERE EC_STEP_INDEX=0
GROUP BY EC_JOB_ID

please let me know how i can get the associated start_time ...for the above query .......

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 03:29:36

sorry i'm getting confused. can you make sure you give data in below format and then explain what you want?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources