Previous Value Subquery not working as needed

  • Thread starter Thread starter DawnTreader
  • Start date Start date
D

DawnTreader

Hello All

i have a query that is trying to find the previous running hours recorded of
a compressor system. the idea is similar to the reading of a electrical use
meter on a house, therefore i used the query from Allen Browne's page.

this is my SQL:

SELECT
subtblRunningHours.RunningHoursID,
subtblRunningHours.RHDate,
subtblRunningHours.HoursAtDate,
subtblRunningHours.ServiceReportID,
subtblRunningHours.BlockID,
subtblRunningHours.IssueID,
(SELECT TOP 1
DsubtblRunningHours.HoursAtDate
FROM
subtblRunningHours AS DsubtblRunningHours
WHERE
DsubtblRunningHours.BlockID = subtblRunningHours.BlockID
AND DsubtblRunningHours.RHDate < subtblRunningHours.RHDate
ORDER BY
DsubtblRunningHours.RHDate DESC,
DsubtblRunningHours.RunningHoursID) AS PriorRH
FROM
subtblRunningHours
ORDER BY
subtblRunningHours.BlockID DESC ,
subtblRunningHours.HoursAtDate DESC;

but the results i am getting are not what i expected. here they are for one
compressor:

RHID RHDate HrsAtDate SRID BlockID IssueID PriorRH
20686 28/05/2009 1040 21949 1675
20684 28/05/2009 1040 1675 3130 1040
20623 06/06/2009 1018 21886 1675 71
20621 06/06/2009 1018 1675 3108 1018
20639 26/06/2009 1009 1675 3111 278
20565 15/06/2009 278 1675 3093 278
20564 15/06/2009 278 21857 1675 110
20588 09/06/2009 110 21863 1675 39
20586 09/06/2009 110 1675 3099 110
20619 04/06/2009 71 21885 1675 1040
20617 04/06/2009 71 1675 3107 71
20303 08/06/2009 39 21575 1675 1018

as you can see some of it works out, but then there are some that dont. for
instance the 2 rows:

20564 15/06/2009 278 21857 1675 110
20588 09/06/2009 110 21863 1675 39

seem to be working, but then you get these:

20684 28/05/2009 1040 1675 3130 1040
20623 06/06/2009 1018 21886 1675 71
20621 06/06/2009 1018 1675 3108 1018

and none of those look like they are working.

does anyone have any ideas as to why? i am thinking i need to find the max
record, then get it to do the one before it, but i like the ability to see
string of values and know that it is increasing.

as always any and all help appreciated
 
Uh oh

this may just be a data entry problem. the whole reason i am trying to
create this query in the first place. post back if it isnt.
 
Hello All

the record i picked does have entry errors so it is not a good example. but
now i have another question.

where would i calculate the difference between the current and previous
record? i want to take the 2 running hours values and figure out the
difference between them, then i want to figure out the days between the 2
dates and then average the difference in hours over the days between the 2
dates. this will help me to see if there are more running hours in a day than
the 24 that the machine could run.

this is all part of validating a users entry.

thanks for any help.
 
Back
Top