sql query time calculation problem!

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 2000 Forums

SQL Server Development (2000)

sql query time calculation problem!

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-10-09 : 07:15:02

Hi,

I have a query that reurns the following rercordset for example

REQUESTID REQUEST ENTERED TIME PROCESS START TIME PROCESS END TIME START TILL END TIME REQUEST TILL END TIME
NHDRRR 2003-10-01 11:37:24.390 2003-10-01 11:37:25.153 2003-10-01 11:41:13.550 00:03:48 00:03:49
KSRELB 2003-10-07 14:12:28.910 2003-10-07 14:12:30.017 2003-10-07 14:28:22.480 00:15:52 00:15:54
EWBWYT 2003-10-07 15:18:35.040 2003-10-07 17:50:10.187 2003-10-07 17:56:49.043 00:06:39 02:38:14
AQFUFZ 2003-10-08 10:15:23.297 2003-10-08 10:15:25.233 2003-10-08 10:22:25.150 00:07:00 00:07:02

The last two columns are calculated fields (i.e. START TILL END TIME is the PROCESS END TIME - PROCESS START TIME and REQUEST TILL END TIME is PROCESS END TIME - REQUEST ENTERED TIME)

now at the bottom of this record set i need to display the following

Average Time from request till delivery: (sum of REQUEST TILL END TIME
for all records / no of records)

Average Time from start of delivery to completion: (sum of START TILL END TIME for all records / no of records)

Can anyone please help how i can do this from my main query which retrieves my record set but can also give me the averages? ALso a bonus would be also to get the calculated fields (last rwo columns) from the main query as well. I've pasted my query below. Any help greatly appreciated.

SELECT ArchiveRequest.requestId AS Expr1, GSF.dbo.sendRequest.requestEnteredTime, GSF.dbo.sendRequest.processStartTime,
GSF.dbo.sendRequest.processEndTime
FROM ArchiveRequest INNER JOIN
GSF.dbo.sendRequest ON ArchiveRequest.archiveRequestId = GSF.dbo.sendRequest.archiveRequestId

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-09 : 08:53:51

Sample data and DDL would help us help you...

but maybe look up ROLLUP in BOL...

[shot in dark]everyone ok?[/shot in dark]

you never know where those damn bullets land...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-10-09 : 09:07:28

I though i HAD provided some sample data! Thanks for the reply but it didn't help me out at all.

All i want is for example if my query returns the following results

ID Request Time Start Time End Time Time from Request to End Time from Start to End
xhgfbvc 17:50:30 17:50:32 17:50:37 00:00:05 00:00:07
cfdsax 18:01:12 18:01:18 18:01:31 00:00:13 00:00:19
cvdzxs 18:03:45 18:03:47 18:03:58 00:00:11 00:00:13

NOW WHAT I WANT IS THE AVERAGE TIME FROM REQUEST TILL DELIVERY I.E

Average Time from request till delivery = sum of REQUEST TILL END TIME
for all records / no of records)

HOW DO I GENERATE THIS FROM MY SQL SELECT STATEMENT THAT RETURNS THE RECORD SET ABOVE??

THANKS :)

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-09 : 09:26:18

OK...you supplied sample results...

no need to SHOUT btw

And did you look at rollup in Books Online?

Try this



USE Northwind
GO

SELECT CustomerId, Sum(Freight) 
  FROM Orders
GROUP BY CustomerId, Freight WITH ROLLUP
GO

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-09 : 10:32:59

Hey Zubair, chill...

Remember help from here is voluntary, free and on our time. Anything you can provide to facilitate a solution is for you own benefit.

DDL - CREATE TABLE statements
Sample Data - INSERT statements

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-10-09 : 11:28:51

sorry guys. Didn't know that caps was shouting. (just wanted to make things clear :) )
Well appreciate the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources