comparing log tables on a time column

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

Transact-SQL (2000)

comparing log tables on a time column

Author

Topic

hanspeter
Starting Member

1 Post

Posted - 2005-01-25 : 12:52:42

Hi,
I have two log tables, one lists parts the other logs the status of the machine which produces the parts.

Parts
Part Time
1 01/02/05 12:33
2 01/02/05 12:45
3 01/03/05 14:22

Status
ID Time Type
1 01/01/05 04:23 2
2 01/02/05 13:40 1
3 01/02/05 20:12 4

Every Status is valid till the next log entry

I want a list which tells me which status was valid when a part was manufactured.

Part P.Time S.Type
1 01/02/05 12:33 2
2 01/02/05 12:45 2
3 01/03/05 14:22 1

Any help on that would be very appreciated

peter

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-25 : 13:38:48

select p.Part, P.Time, Type = (select max(Type) from Status s where s.Time <= p.Time)
from Part p

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources