sql server - calculate sum with number fluctuations -

i'm calculating water flow , have come across situation , need help.

date/time  account   read ---------  --------  ----- 04:00.0    16887084  38665 03:30.0    16887084  38652 **<< reverse flow** 03:00.0    16887084  38660 02:30.0    16887084  38656 

i need calculate amount of water usage (as shown in read col), due reverse flow, sample @ 03:30 rolled 8 gallons , @ next sample had increased 13 gallons. how can calculate true water usage? i've tried following statement using number range representation of usage:

select  serial account,     max(read)- min(read) galstotal,      dbo.database  (dbf_dt > dateadd(hh, -24, getdate()))  

any appreciated...

one approach date , time of last reading within period, , date , time of first reading within period (or of last reading before beginning of period), , use retrieve rows.

select s.serial account      , max(lr.read) - min(fr.read) galstotal   (select d.serial              , min(d.dbf_dt) first_read_dt              , max(d.dbf_dt) last_read_dt           dbo.database d          d.dbf_dt > dateadd(hh, -24, getdate()          group d.serial        ) s   join dbo.database fr     on fr.serial = s.serial     , fr.dbf_dt = s.first_read_dt   join dbo.database lr     on lr.serial = s.serial     , lr.dbf_dt = s.last_read_dt  group     s.serial 

(the min , max aggregates not required if there's guarantee dbf_dt unique given serial. if there's unique constraint on (serial,dbf_dt), min , max can removed, there discriminate in event there more 1 row matching first_read_dt or last_read_dt.

another approach use analytic functions available in sql server accomplish same thing.