[dba-SQLServer] Values for date ranges

David,

I'd be tempted to put the data into a table with an identity and then
self-link the current and next rows. Any estimate on the size of the table
you're working with?

Francis R Harvey III
WB 303, (301)294-3952
harveyf1 at westat.com


> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com 
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf 
> Of David Emerson
> Sent: Sunday, July 18, 2004 10:30 PM
> To: dba-SQLServer at databaseadvisors.com
> Subject: [dba-SQLServer] Values for date ranges
> 
> 
> 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 
<snip>