Arthur Fuller
artful at rogers.com
Thu Jul 22 10:08:18 CDT 2004
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