How to write a query that calculates a value using a previous records value?

Question

I'm trying to figure out the best way to calculate a value that is based on the previous records values. I'm sure it's possible, I just can't figure it out.

Consider a table that has StartTime & EndTime. Along with these values is are 2 different types of wages: OnDuty & OffDuty.

OnDuty = All time between StartTime and EndTime.

OffDuty: All time between the previous EndTime and the current StartTime.

So to calculate OffDuty, we have to take the EndTime of the last record and subtract the StartTime of the current record.

Example:

• OnDuty Wage = $10/hr
• OffDuty Wage = $2/hr

Entry 1:

StartTime: 1/1/2010 @ 8:00 AM
EndTime: 1/1/2010 @ 6:00 PM
Pay = $100 (10 hrs @ $10/hr)

Entry 2:

StartTime: 1/2/2010 @ 8:00 AM
EndTime: 1/2/2010 @ 6:00 PM
Pay = $128 (10 hrs @ $10/hr) + (14 hrs @ $2/hr) 

Entry 3:

StartTime: 1/3/2010 @ 8:00 AM
EndTime: 1/3/2010 @ 10:00 AM
Pay = $48 (2 hrs @ $10/hr) + (14 hrs @ $2/hr) 

Do you see the pattern?

Total Pay = $276 (Entry1.Pay + Entry2.Pay + Entry3.Pay)

Basically, I'm debating as to whether it would be best to:

1. put all of the burden on the database to calculate this value for each detail row, or
2. store the previous record's EndTime with each row (ie. PreviousEndTime?)

I like option 1 better. However, I don't even know how to approach this in terms of HOW to do this without creating a lookup query first (note: I'm using SqlCE). I'm relying heavily on LinqToSql and I'm concerned with performance using Linq, as there could be hundreds of thousands of these "Entry" records that need calculated to provide a singe TotalPay for each employee. To perform that lookup on each detail row would be painful!

On the flip side, I feel that there would be a lot of maintenance involved with using the 'PreviousEndTime' field approach. The user could go back and change the EndTime and the idea of updating the next record's PreviousEndTime sounds like a hack to me...

Thoughts? Ideas? Solutions? :)

Answer 1

I would strongly recommend the option to "put all of the burden on the database to calculate this value for each detail row". This is the stuff databases (and their query languages) are made for.

In Oracle you would use LEAD and LAG.

http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php

In SQL Server, there may be some workarounds, see for example:

http://www.rafael-salas.com/2008/05/t-sql-lead-and-lag-functions.html

Not sure if this is supported in SQL Server CE, though.

Answer 2

There is no need to store the previous row's end time. You can calculate differences with previous rows using the Row_NUMBER() ranking function and then performing a self-join.