Help with the query - 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

Help with the query

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-08 : 16:43:16

Hi Below is my sample data , these are the steps for a job

i need to calculate pend time and runtime of a job following the rules

1. the step index has to be 0
2.runnable time is the least of all runnable values , i.e min(runnable_time)where step index is 0
3.start_time is the start_time associated with the min(runnable_time) where step index is zero
4.finish time is the finish where the step index is NULL

EC_STEP_INDEX EC_RUNNABLE_TIME EC_START_TIME EC_FINISH_TIME EC_JOB_ID
NULL 5/1/2009 8:00:04 5/1/2009 8:00:03 5/1/2009 8:05:29 101408
0 5/1/2009 8:00:05 5/1/2009 8:00:09 5/1/2009 8:03:25 101408
1 5/1/2009 8:03:25 5/1/2009 8:03:25 5/1/2009 8:04:43 101408
2 5/1/2009 8:04:43 5/1/2009 8:04:43 5/1/2009 8:04:51 101408
3 5/1/2009 8:04:51 5/1/2009 8:05:19 5/1/2009 8:05:23 101408
4 5/1/2009 8:05:23 5/1/2009 8:05:23 5/1/2009 8:05:29 101408
0 5/1/2009 8:00:06 5/1/2009 8:00:09 5/1/2009 8:02:40 101408
1 5/1/2009 8:02:40 5/1/2009 8:02:41 5/1/2009 8:02:46 101408
2 5/1/2009 8:02:46 5/1/2009 8:02:46 5/1/2009 8:03:21 101408
3 5/1/2009 8:03:21 5/1/2009 8:03:21 5/1/2009 8:03:25 101408
0 5/1/2009 8:02:40 5/1/2009 8:02:41 5/1/2009 8:02:43 101408
1 5/1/2009 8:02:43 5/1/2009 8:02:43 5/1/2009 8:02:46 101408
0 5/1/2009 8:04:43 5/1/2009 8:04:43 5/1/2009 8:04:48 101408
1 5/1/2009 8:04:48 5/1/2009 8:04:48 5/1/2009 8:04:51 101408
0 5/1/2009 8:04:51 5/1/2009 8:05:19 5/1/2009 8:05:23 101408
0 5/1/2009 8:05:23 5/1/2009 8:05:23 5/1/2009 8:05:29 101408

Rules:
Pend Time = Step index 0 and LEAST of Runnable Time
Run Time = Finish_Time - Start Time for NULL step.
Pend Time for job = 4 seconds 0.09-0.05
Run time for job = 5.20 minutes 5.29-0.09

so the output of the job should be

Pend Time for job = 4 seconds 0.09-0.05
Run time for job = 5.20 minutes 5.29-0.09

could any one help me with query for runtime and pend time?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-08 : 16:52:05

This will give you a start


SELECT *
FROM   (
           SELECT *, ROW_NUMBER() OVER (ORDER BY COALESCE(EC_STEP_INDEX, 99), EC_RUNNABLE_TIME) AS PendTime,
                     ROW_NUMBER() OVER (ORDER BY COALESCE(EC_STEP_INDEX, -1)) AS RunTime
           FROM Table1
) AS d
WHERE 1 IN (PendTime, RunTime)

N 56°04'39.26"
E 12°55'05.63"

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-09 : 03:28:46

hi ,

i want to select the min(runnable_time) and its associated start_time separately to a new table , i have underlined and marked bold the fields which i want above ,

the rules to be followed for the select query are

runnable_time : least of all runnable times where step index is 0
start_time : start_time associated with least runnble_time where step index is 0 ...

I TRIED :

INSERT INTO
dbo.DW_T_PENDTIME_SBA
(EC_JOB_ID,
QUEUE_TIME,
START_TIME
)(
SELECT
E.EC_JOB_ID,
MIN(RUNNABLE_TIME),
START_TIME
FROM DW_T_EC_SBA
WHERE STEP_INDEX = '0'
GROUP BY EC_JOB_ID)

But this gives an error saying start_time is not an aggregate funtion ...i want the start_time associated with the least runnable_time where step_index is 0

please help ...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-09 : 03:56:08

[code]
DECLARE @sample TABLE
(
EC_STEP_INDEX int,
EC_RUNNABLE_TIME datetime,
EC_START_TIME datetime,
EC_FINISH_TIME datetime,
EC_JOB_ID int
)
INSERT INTO @sample
SELECT NULL, '5/1/2009 8:00:04', '5/1/2009 8:00:03', '5/1/2009 8:05:29', 101408 UNION ALL
SELECT 0, '5/1/2009 8:00:05', '5/1/2009 8:00:09', '5/1/2009 8:03:25', 101408 UNION ALL
SELECT 1, '5/1/2009 8:03:25', '5/1/2009 8:03:25', '5/1/2009 8:04:43', 101408 UNION ALL
SELECT 2, '5/1/2009 8:04:43', '5/1/2009 8:04:43', '5/1/2009 8:04:51', 101408 UNION ALL
SELECT 3, '5/1/2009 8:04:51', '5/1/2009 8:05:19', '5/1/2009 8:05:23', 101408 UNION ALL
SELECT 4, '5/1/2009 8:05:23', '5/1/2009 8:05:23', '5/1/2009 8:05:29', 101408 UNION ALL
SELECT 0, '5/1/2009 8:00:06', '5/1/2009 8:00:09', '5/1/2009 8:02:40', 101408 UNION ALL
SELECT 1, '5/1/2009 8:02:40', '5/1/2009 8:02:41', '5/1/2009 8:02:46', 101408 UNION ALL
SELECT 2, '5/1/2009 8:02:46', '5/1/2009 8:02:46', '5/1/2009 8:03:21', 101408 UNION ALL
SELECT 3, '5/1/2009 8:03:21', '5/1/2009 8:03:21', '5/1/2009 8:03:25', 101408 UNION ALL
SELECT 0, '5/1/2009 8:02:40', '5/1/2009 8:02:41', '5/1/2009 8:02:43', 101408 UNION ALL
SELECT 1, '5/1/2009 8:02:43', '5/1/2009 8:02:43', '5/1/2009 8:02:46', 101408 UNION ALL
SELECT 0, '5/1/2009 8:04:43', '5/1/2009 8:04:43', '5/1/2009 8:04:48', 101408 UNION ALL
SELECT 1, '5/1/2009 8:04:48', '5/1/2009 8:04:48', '5/1/2009 8:04:51', 101408 UNION ALL
SELECT 0, '5/1/2009 8:04:51', '5/1/2009 8:05:19', '5/1/2009 8:05:23', 101408 UNION ALL
SELECT 0, '5/1/2009 8:05:23', '5/1/2009 8:05:23', '5/1/2009 8:05:29', 101408

; WITH
pend
AS
(
SELECT *, row_no = row_number() OVER (PARTITION BY EC_JOB_ID ORDER BY EC_RUNNABLE_TIME)
FROM @sample
WHERE EC_STEP_INDEX = 0
),
run
AS
(
SELECT *, row_no = row_number() OVER (PARTITION BY EC_JOB_ID ORDER BY EC_START_TIME)
FROM @sample
WHERE EC_STEP_INDEX IS NULL
)
SELECT p.EC_JOB_ID,
pend_time = DATEDIFF(second, p.EC_RUNNABLE_TIME, p.EC_START_TIME),
run_time = DATEDIFF(second, p.EC_START_TIME, r.EC_FINISH_TIME),
[run_time_in_m.s] = DATEDIFF(minute, p.EC_START_TIME, r.EC_FINISH_TIME) +
(DATEDIFF(second, p.EC_START_TIME, r.EC_FINISH_TIME) % 60) / 100.0
FROM pend p
INNER JOIN run r ON p.EC_JOB_ID = r.EC_JOB_ID
WHERE p.row_no = 1
AND r.row_no = 1

/*
EC_JOB_ID pend_time run_time run_time_in_m.s
----------- ----------- ----------- --------------------
101408 4 320 5.200000

(1 row(s) affected)

*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-09 : 12:38:50

This is giving the min of start_time right?
how would i get the start_time associated with the min ( runnable_time) ..

though the values given HERE are right , i have my data where i need to calculate the difference between min(runnable_time) and its associated start_time ....

quote:
Originally posted by khtan

[code]
SELECT *, row_no = row_number() OVER (PARTITION BY EC_JOB_ID ORDER BY EC_START_TIME)
FROM @sample
WHERE EC_STEP_INDEX IS NULL
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 12:44:28

quote:
Originally posted by rds207

This is giving the min of start_time right?
how would i get the start_time associated with the min ( runnable_time) ..

though the values given HERE are right , i have my data where i need to calculate the difference between min(runnable_time) and its associated start_time ....

quote:
Originally posted by khtan

SELECT *, row_no = row_number() OVER (PARTITION BY EC_JOB_ID ORDER BY EC_START_TIME)
FROM @sample
WHERE EC_STEP_INDEX IS NULL
)

for that you just need this
[code]
SELECT p.EC_JOB_ID,
pend_time = DATEDIFF(second, p.EC_RUNNABLE_TIME, p.EC_START_TIME)
FROM pend p
WHERE p.row_no=1

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-09 : 12:59:14

Thank you for the reply , but actually my question is not about calculating the pend time at this time , first need to get the queuetime and starttime into a seperate then calculate the datediff between them , and this is not the only record i have in my table , i have some thousands of records runnuing jobs parallal ,

thats the reason i have used

INSERT INTO
dbo.DW_T_PENDTIME_SBA
(EC_JOB_ID,
QUEUE_TIME,
START_TIME
)(
SELECT
E.EC_JOB_ID,
MIN(RUNNABLE_TIME),
START_TIME
FROM DW_T_EC_SBA
WHERE STEP_INDEX = '0'
GROUP BY EC_JOB_ID)-----------but i get an error with this,

first to get the data into separate table ,

if i do ,

INSERT INTO
dbo.DW_T_PENDTIME_SBA
(EC_JOB_ID,
QUEUE_TIME,
START_TIME
)(
SELECT
E.EC_JOB_ID,
MIN(RUNNABLE_TIME),
min(START_TIME)
FROM DW_T_EC_SBA
WHERE STEP_INDEX = '0'
GROUP BY EC_JOB_ID)----works fine
---
then the above is working fine , but i am getting the min of starttime and min of runnable _time here for each job id , but am not sure if that min(start_time) is the associated start_time for min(runnable_time) , if we look into the example of data i have given here this works perfect as start_time associated with min(runnable_time) is actually the min(start_time) among all jobs where step_index 0 ...

my question is if its not ?

thats the reason i need to write a simple select query to get the start_time associated with the min(runnable_time) among all step_index =0...

hope am able to explain what i want...

thank you

quote:
Originally posted by visakh16

quote:
Originally posted by rds207

This is giving the min of start_time right?
how would i get the start_time associated with the min ( runnable_time) ..

though the values given HERE are right , i have my data where i need to calculate the difference between min(runnable_time) and its associated start_time ....

quote:
Originally posted by khtan

SELECT *, row_no = row_number() OVER (PARTITION BY EC_JOB_ID ORDER BY EC_START_TIME)
FROM @sample
WHERE EC_STEP_INDEX IS NULL
)

for that you just need this
[code]
SELECT p.EC_JOB_ID,
pend_time = DATEDIFF(second, p.EC_RUNNABLE_TIME, p.EC_START_TIME)
FROM pend p
WHERE p.row_no=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 13:06:37

quote:
Originally posted by rds207

thats the reason i need to write a simple select query to get the start_time associated with the min(runnable_time) among all step_index =0...

hope am able to explain what i want...

thank you

for that do like


; WITH 
pend
AS
(
	SELECT	*, row_no = row_number() OVER (PARTITION BY EC_JOB_ID ORDER BY EC_RUNNABLE_TIME)
	FROM	@sample
	WHERE	EC_STEP_INDEX	= 0
)

INSERT INTO 
dbo.DW_T_PENDTIME_SBA
(EC_JOB_ID,
QUEUE_TIME,
START_TIME
)
SELECT	p.EC_JOB_ID,
	QUEUE_TIME,
START_TIME
FROM	pend p
WHERE p.row_no=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources