[dba-SQLServer] Values for date ranges

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




More information about the dba-SQLServer mailing list