sql - Difference Max and Min from Different Dates -

i'm going try explain best can.

the code below following:

• finds service address servicelocation table.
• finds service type (electric or water).
• finds how many days in past pull data.

once has this, calculates "daily usage" subtracting max meter read day minimum meter read day.

        (max(mr.reading) - min(mr.reading)) 'daytimeusage' 

however, i'm missing max reading day prior , minimum reading current day. mathematically, should this:

• max(priordayreading) - min(readdatereading)

essentially, if goes 5 days should kick out table reads follows:

service location | read date | usage |

123 main st | 4/20/15 | 12 |
123 main st | 4/19/15 | 8 |
123 main st | 4/18/15 | 6 |
123 main st | 4/17/15 | 10 |
123 main st | 4/16/15 | 11 |

where "usage" 'daytimeusage' + usage i'm missing (and question above). example, 4/18/15 'daytimeusage' in query below plus the difference between max read 4/17/15 , min read 4/18/15.

i'm not sure how accomplish or if possible.

select      a.serviceaddress 'service address',      convert(varchar(10),a.readdate,101) 'date',      sum(a.[daytimeusage]) 'usage'    (     select         sl.location_addr 'serviceaddress',          convert(varchar(10),mr.read_date,101) 'readdate',          (max(mr.reading) - min(mr.reading)) 'daytimeusage'             dimservicelocation sl         inner join factbill fb on fb.servicelocationkey = sl.servicelocationkey         inner join factmeterread mr on mr.servicelocationkey = sl.servicelocationkey         inner join dimcustomer c on c.customerkey = fb.customerkey              c.class_name = 'tenant'         , sl.servicelocationkey = @servicelocation         , mr.meter_type = @servicetype     group          sl.location_addr,          convert(varchar(10),         mr.read_date,101) )  a.readdate >= getdate()-@days  group a.serviceaddress, convert(varchar(10),a.readdate,101) order convert(varchar(10),a.readdate,101) desc 

you can use apply operator if above sql server 2005. here link documentation. https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx apply operation comes in 2 forms outer apply , cross apply - outer works left join , cross works inner join. let run query once each row returned. setup own sample of trying do, here , hope helps.

http://sqlfiddle.com/#!6/fdb3f/1

create table sequencedvalues (   location varchar(50) not null,   calendardate datetime not null,   reading int   )  insert sequencedvalues (   location,   calendardate,   reading   )  select    'address1',    '4/20/2015',    10 union select    'address1',    '4/19/2015',    9 union select    'address1',    '4/19/2015',    20 union select    'address1',    '4/19/2015',    25 union select    'address1',    '4/18/2015',    8 union select    'address1',    '4/17/2015',    7 union select    'address2',    '4/20/2015',    100 union select    'address2',    '4/20/2015',    111 union select    'address2',    '4/19/2015',    50 union select    'address2',    '4/19/2015',    65   select distinct      sv.location,     sv.calendardate,     sv_dayof.mindayofreading,     sv_daybefore.maxdaybeforereading sequencedvalues sv outer apply (     select min(sv_dayof_inside.reading) mindayofreading     sequencedvalues sv_dayof_inside     sv.location = sv_dayof_inside.location     , sv.calendardate = sv_dayof_inside.calendardate     ) sv_dayof outer apply (     select max(sv_daybefore_max.reading) maxdaybeforereading     sequencedvalues sv_daybefore_max     sv.location = sv_daybefore_max.location     , sv_daybefore_max.calendardate in (         select top 1 sv_daybefore_inside.calendardate         sequencedvalues sv_daybefore_inside         sv.location = sv_daybefore_inside.location         , sv.calendardate > sv_daybefore_inside.calendardate         order sv_daybefore_inside.calendardate desc         )     ) sv_daybefore order     sv.location,     sv.calendardate desc