Elapsed time computation:SQL 2000

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

Elapsed time computation:SQL 2000

Author

Topic

newbie007
Starting Member

37 Posts

Posted - 2009-03-19 : 11:17:52

I have the following data in my table

Name Starttime Startday Endtime Endday

A 71700 1 71900 1
B 213100 1 214800 1
C 235500 1 001000 2

( ie Name - A, Starttime -71700,Startday -1,Endtime -71900,Endday -1 & so on)

1) As what date type do I enter Starttime and Endtime
2)What query will give me the elapsed time in minutes , ie, EndTime-Starttime (also factoring in startday and endday. for eg 3rd data point: endday>startday)
3)How can i get the 'elapsed time' added as a column onto my existing table ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 11:23:18

what does 71700 represents? seconds since start of day?

newbie007
Starting Member

37 Posts

Posted - 2009-03-19 : 11:27:12

quote:
Originally posted by visakh16

what does 71700 represents? seconds since start of day?

7 - hrs
17 - mins
00 -secs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 11:39:57

1,store start and end times as int
2, to get elapsed time use this
SELECT DATEDIFF(mi,
DATEADD(dd,Startday,DATEADD(hh,StartTime/10000,DATEADD(mi,(StartTime%10000)/100,DATEADD(ss,StartTime%100,0)))),DATEADD(dd,Endday,DATEADD(hh,EndTime/10000,DATEADD(mi,(EndTime%10000)/100,DATEADD(ss, EndTime%100,0)))))

3,ALTER TABLE <tablenamehere> ADD ElapsedTime int

UPDATE <tablenamehere>
SET ElapsedTime =DATEDIFF(mi,
DATEADD(dd,Startday,DATEADD(hh,StartTime/10000,DATEADD(mi,(StartTime%10000)/100,DATEADD(ss,StartTime%100,0)))),DATEADD(dd,Endday,DATEADD(hh,EndTime/10000,DATEADD(mi,(EndTime%10000)/100,DATEADD(ss, EndTime%100,0)))))

newbie007
Starting Member

37 Posts

Posted - 2009-03-19 : 12:17:26

quote:
Originally posted by visakh16

1,store start and end times as int
2, to get elapsed time use this
SELECT DATEDIFF(mi,
DATEADD(dd,Startday,DATEADD(hh,StartTime/10000,DATEADD(mi,(StartTime%10000)/100,DATEADD(ss,StartTime%100,0)))),DATEADD(dd,Endday,DATEADD(hh,EndTime/10000,DATEADD(mi,(EndTime%10000)/100,DATEADD(ss, EndTime%100,0)))))

3,ALTER TABLE <tablenamehere> ADD ElapsedTime int

UPDATE <tablenamehere>
SET ElapsedTime =DATEDIFF(mi,
DATEADD(dd,Startday,DATEADD(hh,StartTime/10000,DATEADD(mi,(StartTime%10000)/100,DATEADD(ss,StartTime%100,0)))),DATEADD(dd,Endday,DATEADD(hh,EndTime/10000,DATEADD(mi,(EndTime%10000)/100,DATEADD(ss, EndTime%100,0)))))

Thanx a ton Visakh ! Only 1 problem - it returns negative value when startday =7 and endday = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 12:42:19

how will start day be after endday? can you explian that?

newbie007
Starting Member

37 Posts

Posted - 2009-03-19 : 13:23:14

quote:
Originally posted by visakh16

how will start day be after endday? can you explian that?

start/end day numbers are used to indicate different days . 1 = sun ...7 = sat.so there could be cases when the person starts an action on a sat(7) and ends it on sun (1 - of the next week), under no circumstance will it spill over to the 2nd day of the next week. In other words, the day difference will always be 1 day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 13:50:53

oh i see. in such case where will you be having date information stored?

newbie007
Starting Member

37 Posts

Posted - 2009-03-20 : 01:33:52

quote:
Originally posted by visakh16

oh i see. in such case where will you be having date information stored?

there is another column for date - entries are in the foll format

6 730
6 731
6 8 1
6 8 2
6 8 3
6 8 4
6 8 5

(ie, 30th jul 2006 to 5th august 2006)

Is it possible to work it out without dates also - as the problem happens only for startday =7 endday =1 cases

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 09:33:45

nope...you need to consider date also. Or else how will you determine whether it spans over single or multiple weeks?

newbie007
Starting Member

37 Posts

Posted - 2009-03-20 : 10:18:18

quote:
Originally posted by visakh16

nope...you need to consider date also. Or else how will you determine whether it spans over single or multiple weeks?

ok - how does one do it with dates (expressed in the format i mentioned in my earlier post) ?

what i meant (w/o dates) was - since the problem is only where startday=7 and endday=1, is it possible to give an additionl separate query with that condition.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 10:23:18

so you will have two date fields too? start and end?

newbie007
Starting Member

37 Posts

Posted - 2009-03-20 : 11:27:52

quote:
Originally posted by visakh16

so you will have two date fields too? start and end?

there is only one date field and it follows a different format. the date changes only when the time reaches 020000. so you have no conventional relation between the dates and the day because each 'date ' will correspond to 2 days. Pls see the data below.
days change when time reaches 000000 but dates change only when it becomes 020000.
[URL=http://img26.imageshack.us/my.php?image=datau.png]

[/URL]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 12:34:11

[code]SELECT DATEDIFF(mi,
DATEADD(dd,CASE WHEN Startday>Endday THEN 7-Startday ELSE Startday END,DATEADD(hh,StartTime/10000,DATEADD(mi,(StartTime%10000)/100,DATEADD(ss,StartTime%100,0)))),DATEADD(dd,Endday,DATEADD(hh,EndTime/10000,DATEADD(mi,(EndTime%10000)/100,DATEADD(ss, EndTime%100,0)))))
[/code]

newbie007
Starting Member

37 Posts

Posted - 2009-03-20 : 13:40:29

quote:

Originally posted by visakh16

SELECT DATEDIFF(mi,
DATEADD(dd,CASE WHEN Startday>Endday THEN 7-Startday ELSE Startday END,DATEADD(hh,StartTime/10000,DATEADD(mi,(StartTime%10000)/100,DATEADD(ss,StartTime%100,0)))),DATEADD(dd,Endday,DATEADD(hh,EndTime/10000,DATEADD(mi,(EndTime%10000)/100,DATEADD(ss, EndTime%100,0)))))

Thanx again Visakh - works fine now

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 13:41:19

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources