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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-06-23 : 10:38:48
|
| Peter writes "aye that is right if I do the query one way I get all the data ... if I do the query a different way mining for different information I loose some data somehow.I have some data which changes once ever hour ... when the data changes it indicates that the thing attached is going to sleep. I want to do a query which checks how long it is on for. THe query that calulates how long it is sleeping which is the opposite of what is below works perfectly well. But this query works fine until i get to 2004/06/14 it is missing the 2am entry. Yes that's right totally missed a h2.datetime record which does exist.select h1.name, h1.datetime, h2.datetimefrom history h1 inner join history h2 on h1.name = h2.name and h1.datetime < h2.datetimewhere h1.name = '01Fault' and h1.datetime between '2004/06/10' and '2004/06/17' and h1.value = 0 and h2.value = 1 and h2.datetime between '2004/06/10' and '2004/06/17' and h2.datetime =(select min(h3.datetime) from history h3 where h3.value = 1 and h3.name = h1.name and h3.datetime between '2004/06/10' and '2004/06/17' and h3.datetime > h1.datetime group by h3.name)" |
|
|
|
|
|
Correlated subquery loosing data