[dba-SQLServer] Values for date ranges

Thanks Arthur,

I ended up needing to us a cursor in the end because there were other 
factors like needing to select records for a range of dates and changing 
the first and last dates if they were not exactly on the range.  I also 
needed to make sure that if there was only one record that it was included 
if it fell within the range.

The ideas everyone gave me will certainly be able to be used in the future.

David

At 22/07/2004, you wrote:
>Just improvising here, so this is not genuine SQL, but....
>
>Join the table to itself, but the second instance is something like
>SELECT TOP 1 WHERE Date > MyCurrentDate (and of course the join occurs
>on the PK), so that the joined data looks something like this (you
>didn't mention the PK so I've added it in parens):
>
>(pk)    7/1/04     10   (pk)    7/5/04     12
>
>Assuming that you can join the tables like this, then it's trivial to
>subtract the first date from the second to give you the number of days.
>
>Does this help?
>Arthur
>
>-----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
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com