Here's one way:declare @cust_fcst table (file_date datetime, file_crq int)insert @cust_fcstselect '2009-03-25', 30500 union allselect '2009-04-01', 29000 union allselect '2009-04-08', 29000 union allselect '2009-04-15', 27000 union allselect '2009-04-22', 28500;with cteas(select file_date ,sum(file_crq) qty ,row_number() over (order by file_date) as rnfrom @cust_fcstgroup by file_date)select a.file_date ,a.qty ,a.qty-isNull(b.qty, a.qty) as changefrom cte aleft outer join cte b on b.rn = a.rn - 1OUTPUT:file_date qty change----------------------- ----------- -----------2009-03-25 00:00:00.000 30500 02009-04-01 00:00:00.000 29000 -15002009-04-08 00:00:00.000 29000 02009-04-15 00:00:00.000 27000 -20002009-04-22 00:00:00.000 28500 1500
Be One with the OptimizerTG 
Quantity change between returned query rows
Posted - 2009-05-11 : 14:14:40
The one thing all set-based methods will have in common is a way to correlate a row in one instance of the data with the sequentially previous row of another instance of the table. That could be done using a self join with the table itself, two identical derived tables, 2 instances of a CTE, etc. The reason I used a CTE was that was a simple way to create a sequence number once and re-use it.You could also use an OUTER APPLY to get the top 1 qty for each row ordered by file_date desc where the file_date is less than the "current" file_date.Then of course there are non-set-based methods as well but they won't be as efficient.Be One with the OptimizerTG