Subtracting Dates for average times

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)

Subtracting Dates for average times

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-09-25 : 07:59:22

I have a Database with two tables. This is a production operation. Each table represents ingredients measures from two diferent tanks. I am trying to find the time difference between the two. This would tell us what the batch times are. The two tanks report the data at seperate times (minutes apart). Also I'd like to know the avgerage times.

Here is what I have so far:

SELECT Date_Time
FROM (select Date_Time FROM Precip1Production
UNION ALL
SELECT Date_Time FROM Precip2Production) s

This produces all the times, now I need to 1st subtract the diference between them and 2nd find the average.

This may seem elementary to some of you, but I am still new to the SQL world. Any help you can provide would be greatly appreciated.

Thanks,

John

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-25 : 08:08:02

Your request is unclear (at least to me).

Please supply the DDL (create table statements) used to create the two tables, some sample data and the expected resultset.

Jay White
{0}

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-09-25 : 08:36:15

In agreeing with page47, may i add....that you need to be able to link the data between the 2 tables....ie....how do you know from the following data set which tank is reporting which time...

12.00
12.05
12.16
12.18
12.35
12.39
12.42

because that is what your UNION ALL query will display...

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-09-25 : 08:42:00

The tables are already created.

Precip1Production
Precip2production

The column in the tables are the same name and represent the times each row was reported. The column names are Date_Time

When I run the existing query I get results such as:

1) 2002-09-20 18:10:32.550 (ex. Precip1Production.Date_Time)
2) 2002-09-20 19:20:02.870 (ex. Precip2Production.Date_Time)
3) 2002-09-20 20:29:03.040 (ex. Precip1Production.Date_Time)
4) 2002-09-20 21:40:53.710 (ex. Precip2Production.Date_Time)

Heres what I need to do:
1st
If I subtract 1 from 2 I get 1:09:30. If I subtract 2 from 3 I get 1:09:00, 3 from 4 is 1:11:51, etc. This tells us the batch times (between batches).
2nd
Then adding all three (1:09:30+1:09:00+1:11:51)/3 = average batch times. I am looking to do this in one or possibly two statements.

I hope this helps clear up my request.

Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-09-25 : 08:44:21

you also should consider merging the 2 tables (Precip1Production
, Precip2Production) into 1 table....Precipxxxx (and prefix each row with a new column tanknumber.) this will alow you to expand the number of tanks you work with without having to redesign (seriously) your database or code....

This holds true especially if there is no significant difference in the information recorded about each tank...or any relationships between any one tank and other data items...compared to the other tank.

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-09-25 : 09:10:44

This database is very new and still being developed. I have considered making only one table. I need to figure a way to identify which record is reported by which Precip tank (1 or 2).

I am open to any suggestions at this point.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-09-25 : 12:08:52

you 1st state 'tables already created'...and then say 'still being developed'.....which leads me to urge you to consolidate the tables into 1...."because you still can...nothing is in production yet...therefore if a change is necessary, just do it"

remember what happens if a 3rd tank comes along into use....under your current design you will need another table and to amend all relevent queries....when really a 3rd tank, should jsut be anoher record in the tank table.

if you do need to persist with the original design...then

select 1, datetime from tank1
union all
select 2, datetime from tank2

may get you going in the right direction....

but you will then run into problems, as to deciding which starttime in tank1 goes with which endtime in tank2....(you have yet to indicate how you positively identfy seperate batches...(and not just infer from the relationship between 1 starttime and 1 other endtime...(what happens if the timer on each of the tanks gets out of sync???)

also SQL is a SET processing language....and you will run into problems (unless you go for CURSORS, which are a big no-no around here for performance reasans) with doing calculations on 2 or more different rows...

while it may look simple to do row2-row1=5, row3-row3 = 37 based on the data below...

row1 10
row2 15
row3 37

that's not so easy in SQL....a SQL doesn't automatically know/remember anything about row1/2 when processing row2/3...

in order to do the above maths...you need to get the data into the following form...

row1/2...10...15
row2/3...15...37

now you can do a calculation on each row....15-10 and 37-15.

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-09-26 : 07:30:14

Ok I have decided to redesign this. You guys convinced me. There will be one table. There will be one column called Tank_Number that will identify if it came from 1 or 2. My reluctance in doing so is that there is many custom configurations in our HMI package that links to the SQL database. All will need to be changed.

In any case, now is the time. I still have not got an answer on the datetime question. Assuming all these dates are in the same table, and there is now an "identifier" column, how would I get my time deltas and averages for the selected query?

Thanks,

John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources