[dba-SQLServer] Values for date ranges

David Emerson davide at dalyn.co.nz
Tue Jul 20 14:21:49 CDT 2004


Thanks Stuart and Francis once again.  I have decided to go the way of the 
cursors because there are extra steps needed such as setting the first and 
last dates (each could be between records).

David

At 20/07/2004, you wrote:
>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>
>_______________________________________________
>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