[dba-SQLServer] Values for date ranges

David Emerson davide at dalyn.co.nz
Sun Jul 18 21:29:41 CDT 2004


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).

Regards

David Emerson
Dalyn Software Ltd
25 Cunliffe St, Churton Park
Wellington, New Zealand
Ph/Fax (04) 478-7456
Mobile 027-280-9348 




More information about the dba-SQLServer mailing list