[dba-SQLServer] Values for date ranges

On 19 Jul 2004 at 14:29, David Emerson wrote:

> Group,
> 
> I have a table with a field for date, and a field for a number.  The number 
> relates to all days from the date until the next record when the new date 
> provides a new number.  EG
> 
> 7/1/04     10
> 7/5/04     12
> 7/8/04     15
> 7/15/04    18
> 
> The number 10 applies to 7/1 to 7/4 (providing a total of 10 * 4 days = 40)
> The number 12 applies to 7/5 to 7/7 (providing a total of 12 * 3 days = 36)
> The number 15 applies to 7/8 to 7/14 (providing a total of 15 * 7 days = 105)
> 
> I need to write a sproc that calculates the number of days between each 
> date and adds up the numbers applying to those dates.  I can only think of 
> using a cursor and looping through the records recording the start and end 
> dates, and making the calculations.  Before I launch into actual code I 
> thought I would check to see if there was any quicker way of doing it 
> (apart from changing the table to record first and last dates).
> 

Haven't got time now to do the whole thiing, I'm flying out in an hour or so to 
do so work for a client on site but....

In pseudocode, I would try something like

Sum(numbers *  datediff (currentrecorddate, (select max(date) from table where 
date < currentrecorddate)))


 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.