Total minutes elapsed - 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 2000 Forums

SQL Server Development (2000)

Total minutes elapsed

Author

Topic

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-01-08 : 10:18:14

Hi

I have quite similar problem as being discussed in these forums regarding calculating minutes from starttime and stoptime.
I am engineering student , i need to compute number of processes executed in given interval say for example 5 minutes.

Consider the following process alpha starts at 13:10:09(starttime) and its duration is 240 seconds . I found out it end time by simply using dateadd function. So now i have its stop time 13:14:09(stoptime).

but the way i want to store this in my table is as follows

starttime , count
13:10:09 , 1
13:11:09 , 1
13:12:09 , 1
13:13:09 , 1
13:14:09 , 1

if i can do this for one process i should be able to run for my all processes , which would tell me by looking at a count value , of how many processes were running @ 13:13:09.

I would really appreciate your help.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 10:22:16

[code]
DECLARE @StartTime datetime,@EndTime datetime
SELECT @StartTime='13:10:09'
SELECT @EndTime=DATEADD(ss,240,@StartTime)

SELECT DATEADD(ss,60*number,@StartTime)
FROM master..spt_values
WHERE type='p'
AND DATEADD(ss,60*number,@StartTime)<=@EndTime
[/code]

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-01-08 : 14:54:29

Wow! this is great . Thanks

your query is taking 2 parameters , starttime and ss(seconds).
what if i have table like this

starttime , stoptime ,duration
2009-01-07 23:27:31 2009-01-08 00:04:48.000 2237
2009-01-07 23:50:31 2009-01-08 00:05:55.000 924
2009-01-07 23:45:21 2009-01-08 00:08:14.000 1373

can it take values of starttime and duration from a table.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 11:41:12

quote:
Originally posted by ahmadjamalkhan

Wow! this is great . Thanks

your query is taking 2 parameters , starttime and ss(seconds).
what if i have table like this

starttime , stoptime ,duration
2009-01-07 23:27:31 2009-01-08 00:04:48.000 2237
2009-01-07 23:50:31 2009-01-08 00:05:55.000 924
2009-01-07 23:45:21 2009-01-08 00:08:14.000 1373

can it take values of starttime and duration from a table.

Thanks

why not you can. but instead of master..spt_values you need to use your own tables. In sql 2000, master..spt_values contain only less number of records so if difference is big, you need to use your own count table having sufficient records

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources