[dba-SQLServer] Values for date ranges

Francis,

The table is not large at this stage (only 700 records with about 60 added 
each month).

I think that what you are suggesting is what I envisaged I need to do - 
just looking to see if there were any easy tricks.

David

At 19/07/2004, you wrote:
>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>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com

Regards

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