"Jim Abel" <JimA***@discussions.microsoft.com> wrote in message
news:C21739AE-9537-4A01-BCE6-845A4DBDD2CF@microsoft.com...
>I have a table that I use a DTS to insert a date and cont value each day.  I
> add a computed column called isweekday that gets a Y or N depending in the
> date.  I now want to add another computed column for the mean of the counts
> over the past 30 days, one value for based on weekdays and another value for
> the weekend dates.
> The code that I have used in a SQL update statement is
>
> (CONVERT(int, CASE WHEN IsWeekday = 'Y'
>                           THEN (SELECT AVG(RCOUNT)
>                                        FROM RollingRecordCount
>                                        WHERE ISWeekday = 'Y' AND CDATE
> BETWEEN (DATEADD(Day, -30, CDATE)) AND (CDATE))
>                            ELSE (SELECT AVG(RCOUNT)
>                                       FROM RollingRecordCount
>                                       WHERE ISWeekday = 'N' AND CDATE
> BETWEEN (DATEADD(Day, -30, CDATE)) AND (CDATE))
>                          END))
>
>
> When I put this into the formula  in the design of the table for the mean
> field I get the following werror
>
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Subqueries are
> not supported in
> COMPUTED COLUMN constraints, table 'Tmp_RollingRecordCount'.
>
>
> Do you have a way that I can ackomplish this without the subqueries?
>
> Thanks
>
>

"Tibor Karaszi" wrote:

> Computed columns cannot operate over anything but the "current row", just imagine the perf
> implications if otherwise (along with technical implementation etc). How about exposing the derived
> data through a view instead? Or, if OK that data can be stale, have a batch job that does the
> calculations into a normal column.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "Jim Abel" <JimA***@discussions.microsoft.com> wrote in message
> news:C21739AE-9537-4A01-BCE6-845A4DBDD2CF@microsoft.com...
> >I have a table that I use a DTS to insert a date and cont value each day.  I
> > add a computed column called isweekday that gets a Y or N depending in the
> > date.  I now want to add another computed column for the mean of the counts
> > over the past 30 days, one value for based on weekdays and another value for
> > the weekend dates.
> > The code that I have used in a SQL update statement is
> >
> > (CONVERT(int, CASE WHEN IsWeekday = 'Y'
> >                           THEN (SELECT AVG(RCOUNT)
> >                                        FROM RollingRecordCount
> >                                        WHERE ISWeekday = 'Y' AND CDATE
> > BETWEEN (DATEADD(Day, -30, CDATE)) AND (CDATE))
> >                            ELSE (SELECT AVG(RCOUNT)
> >                                       FROM RollingRecordCount
> >                                       WHERE ISWeekday = 'N' AND CDATE
> > BETWEEN (DATEADD(Day, -30, CDATE)) AND (CDATE))
> >                          END))
> >
> >
> > When I put this into the formula  in the design of the table for the mean
> > field I get the following werror
> >
> > - Unable to modify table.
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Subqueries are
> > not supported in
> > COMPUTED COLUMN constraints, table 'Tmp_RollingRecordCount'.
> >
> >
> > Do you have a way that I can ackomplish this without the subqueries?
> >
> > Thanks
> >
> >
>
>