[dba-SQLServer] Values for date ranges

David,

Maybe:

SELECT SUM(PeriodSum) AS PeriodTotalSum
FROM
    (
    SELECT 
        t2.PeriodValue *
        DateDiff
            (day,t2.PeriodStart, 
                (
                SELECT MIN(t1.PeriodStart)
                FROM dbo.Period as t1
                WHERE t1.PeriodStart > t2.PeriodStart
                )
            ) AS PeriodSum
    FROM dbo.Period as t2
    WHERE
        (
        SELECT MIN(t1.PeriodStart)
        FROM dbo.Period as t1
        WHERE t1.PeriodStart > t2.PeriodStart
        ) IS NOT NULL
    ) as t3

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: Tuesday, July 20, 2004 2:16 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Values for date ranges
> 
> 
> 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
<snip>