David Emerson
davide at dalyn.co.nz
Tue Jul 20 01:16:10 CDT 2004
Thanks Stuart. I am having trouble converting your pseudocode into SQL. >Sum(numbers * datediff (currentrecorddate, (select max(date) from table >where >date < currentrecorddate))) How can I reference currentrecorddate in the Select part when it is not part of the select statement? David At 19/07/2004, you wrote: >On 19 Jul 2004 at 14:29, David Emerson wrote: > > > 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). > > > >Haven't got time now to do the whole thiing, I'm flying out in an hour or >so to >do so work for a client on site but.... > >In pseudocode, I would try something like > >Sum(numbers * datediff (currentrecorddate, (select max(date) from table >where >date < currentrecorddate))) > > > >-- >Lexacorp Ltd >http://www.lexacorp.com.pg >Information Technology Consultancy, Software Development,System Support. > > > >_______________________________________________ >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