Another Suggestion

i am new at this. but i need to do a calculation that has to sum a running
value or running value of a running value . here is my expression.
=sum((((Fields!PART_QTY_ON_HAND.Value
- runningvalue((Fields!REQUIREMENT.Value), sum, "tbl1grp3")
* Fields!Temp_Part_Cost.Value ) + .005) * Fields!ANNUAL_CARRYING_RATE.
Value / 1200))
anyone have another suggestion or at least tell me that it can not be done.
thanx, kerries
--
Message posted via http://www.sqlmonster.comKerrie,
I'm not sure what you are asking here. In principle the expression
looks fine although I think your parantheses are in the wrong place. If
you are after a running total of this expression, I just tried a
RunningValue on a ReportItems object, but it didn't like it.
I'd suggest it would be more efficient to try and do this in the SQL
query if you can.
Chris
Kerrie S via SQLMonster.com wrote:
> i am new at this. but i need to do a calculation that has to sum a
> running value or running value of a running value . here is my
> expression.
> =sum((((Fields!PART_QTY_ON_HAND.Value
> - runningvalue((Fields!REQUIREMENT.Value), sum, "tbl1grp3")
> * Fields!Temp_Part_Cost.Value ) + .005) *
> Fields!ANNUAL_CARRYING_RATE. Value / 1200))
> anyone have another suggestion or at least tell me that it can not be
> done. thanx, kerries

Another Check Constraint Error.

I have created a check constraint on a field called Date in a table called
StudentInfo. In the constraint expression I put Date = GETDATE() and
unchecked the "check existing date on creation" and the "enforce constraint
for replication" check boxes. I left the "Enforce constraint for INSERTs and
UPDATEs" checked. However, when I teachers tried to update this field this
morning, and enter today's date, they all got check constraint errors. Does
anybody have any idea what might be wrong? All I want is for the Date field
to only accept the current date when being updated.
Any help would be appreciated, I only have 9 teachers but, everyday at least
1 will get the date wrong.
Thanks.
KevinKevin,
GETDATE returns the current date and time accurate to 1/300 of a
second. It's very unlikely that anyone entering data by hand would
happen to enter precisely this value. If you want to get a date-only
for today into the table, instead of having the teacher enter it, why
not use a default on that column of
DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
column list and values the teacher enters?
SK
Kevin Sinclair wrote:
>I have created a check constraint on a field called Date in a table called
>StudentInfo. In the constraint expression I put Date = GETDATE() and
>unchecked the "check existing date on creation" and the "enforce constraint
>for replication" check boxes. I left the "Enforce constraint for INSERTs and
>UPDATEs" checked. However, when I teachers tried to update this field this
>morning, and enter today's date, they all got check constraint errors. Does
>anybody have any idea what might be wrong? All I want is for the Date field
>to only accept the current date when being updated.
>Any help would be appreciated, I only have 9 teachers but, everyday at least
>1 will get the date wrong.
>Thanks.
>Kevin
>
>|||I can't use a default because there is already a date in the field. What is
supposed to happen is the teachers update this field when they do their
daily attendance. However, at least 1 teacher every day, will input the
wrong date. When this database was in access I used a validation rule on
this field, I need a way to do the same thing in SQL Server 2000.
Thanks.
Kevin
"Steve Kass" <skass@.drew.edu> wrote in message
news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
> Kevin,
> GETDATE returns the current date and time accurate to 1/300 of a
> second. It's very unlikely that anyone entering data by hand would
> happen to enter precisely this value. If you want to get a date-only
> for today into the table, instead of having the teacher enter it, why
> not use a default on that column of
> DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
> column list and values the teacher enters?
> SK
> Kevin Sinclair wrote:
> >I have created a check constraint on a field called Date in a table
called
> >StudentInfo. In the constraint expression I put Date = GETDATE() and
> >unchecked the "check existing date on creation" and the "enforce
constraint
> >for replication" check boxes. I left the "Enforce constraint for INSERTs
and
> >UPDATEs" checked. However, when I teachers tried to update this field
this
> >morning, and enter today's date, they all got check constraint errors.
Does
> >anybody have any idea what might be wrong? All I want is for the Date
field
> >to only accept the current date when being updated.
> >
> >Any help would be appreciated, I only have 9 teachers but, everyday at
least
> >1 will get the date wrong.
> >
> >Thanks.
> >
> >Kevin
> >
> >
> >
> >
>|||Kevin,
You can check for the same value I was suggesting as a default:
create table Kevin (
...
dateCol datetime CHECK (dateCol = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))
...
Steve
Kevin Sinclair wrote:
>I can't use a default because there is already a date in the field. What is
>supposed to happen is the teachers update this field when they do their
>daily attendance. However, at least 1 teacher every day, will input the
>wrong date. When this database was in access I used a validation rule on
>this field, I need a way to do the same thing in SQL Server 2000.
>Thanks.
>Kevin
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
>
>>Kevin,
>> GETDATE returns the current date and time accurate to 1/300 of a
>>second. It's very unlikely that anyone entering data by hand would
>>happen to enter precisely this value. If you want to get a date-only
>>for today into the table, instead of having the teacher enter it, why
>>not use a default on that column of
>>DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
>>column list and values the teacher enters?
>>SK
>>Kevin Sinclair wrote:
>>
>>I have created a check constraint on a field called Date in a table
>>
>called
>
>>StudentInfo. In the constraint expression I put Date = GETDATE() and
>>unchecked the "check existing date on creation" and the "enforce
>>
>constraint
>
>>for replication" check boxes. I left the "Enforce constraint for INSERTs
>>
>and
>
>>UPDATEs" checked. However, when I teachers tried to update this field
>>
>this
>
>>morning, and enter today's date, they all got check constraint errors.
>>
>Does
>
>>anybody have any idea what might be wrong? All I want is for the Date
>>
>field
>
>>to only accept the current date when being updated.
>>Any help would be appreciated, I only have 9 teachers but, everyday at
>>
>least
>
>>1 will get the date wrong.
>>Thanks.
>>Kevin
>>
>>
>>
>
>|||Oops. Ignore my previous message. The check constraint will fail
tomorrow if you update the row! You may need to use a trigger. What
you could do is put in the default and execute the update with:
UPDATE Attendance SET
this = whatever,
that = whatever,
dateCol = DEFAULT
WHERE
keyCol = theRowBeingEdited
SK
Kevin Sinclair wrote:
>I can't use a default because there is already a date in the field. What is
>supposed to happen is the teachers update this field when they do their
>daily attendance. However, at least 1 teacher every day, will input the
>wrong date. When this database was in access I used a validation rule on
>this field, I need a way to do the same thing in SQL Server 2000.
>Thanks.
>Kevin
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
>
>>Kevin,
>> GETDATE returns the current date and time accurate to 1/300 of a
>>second. It's very unlikely that anyone entering data by hand would
>>happen to enter precisely this value. If you want to get a date-only
>>for today into the table, instead of having the teacher enter it, why
>>not use a default on that column of
>>DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
>>column list and values the teacher enters?
>>SK
>>Kevin Sinclair wrote:
>>
>>I have created a check constraint on a field called Date in a table
>>
>called
>
>>StudentInfo. In the constraint expression I put Date = GETDATE() and
>>unchecked the "check existing date on creation" and the "enforce
>>
>constraint
>
>>for replication" check boxes. I left the "Enforce constraint for INSERTs
>>
>and
>
>>UPDATEs" checked. However, when I teachers tried to update this field
>>
>this
>
>>morning, and enter today's date, they all got check constraint errors.
>>
>Does
>
>>anybody have any idea what might be wrong? All I want is for the Date
>>
>field
>
>>to only accept the current date when being updated.
>>Any help would be appreciated, I only have 9 teachers but, everyday at
>>
>least
>
>>1 will get the date wrong.
>>Thanks.
>>Kevin
>>
>>
>>
>
>

Another Check Constraint Error.

I have created a check constraint on a field called Date in a table called
StudentInfo. In the constraint expression I put Date = GETDATE() and
unchecked the "check existing date on creation" and the "enforce constraint
for replication" check boxes. I left the "Enforce constraint for INSERTs and
UPDATEs" checked. However, when I teachers tried to update this field this
morning, and enter today's date, they all got check constraint errors. Does
anybody have any idea what might be wrong? All I want is for the Date field
to only accept the current date when being updated.
Any help would be appreciated, I only have 9 teachers but, everyday at least
1 will get the date wrong.
Thanks.
KevinKevin,
GETDATE returns the current date and time accurate to 1/300 of a
second. It's very unlikely that anyone entering data by hand would
happen to enter precisely this value. If you want to get a date-only
for today into the table, instead of having the teacher enter it, why
not use a default on that column of
DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
column list and values the teacher enters?
SK
Kevin Sinclair wrote:

>I have created a check constraint on a field called Date in a table called
>StudentInfo. In the constraint expression I put Date = GETDATE() and
>unchecked the "check existing date on creation" and the "enforce constraint
>for replication" check boxes. I left the "Enforce constraint for INSERTs an
d
>UPDATEs" checked. However, when I teachers tried to update this field this
>morning, and enter today's date, they all got check constraint errors. Doe
s
>anybody have any idea what might be wrong? All I want is for the Date field
>to only accept the current date when being updated.
>Any help would be appreciated, I only have 9 teachers but, everyday at leas
t
>1 will get the date wrong.
>Thanks.
>Kevin
>
>|||I can't use a default because there is already a date in the field. What is
supposed to happen is the teachers update this field when they do their
daily attendance. However, at least 1 teacher every day, will input the
wrong date. When this database was in access I used a validation rule on
this field, I need a way to do the same thing in SQL Server 2000.
Thanks.
Kevin
"Steve Kass" <skass@.drew.edu> wrote in message
news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
> Kevin,
> GETDATE returns the current date and time accurate to 1/300 of a
> second. It's very unlikely that anyone entering data by hand would
> happen to enter precisely this value. If you want to get a date-only
> for today into the table, instead of having the teacher enter it, why
> not use a default on that column of
> DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
> column list and values the teacher enters?
> SK
> Kevin Sinclair wrote:
>
called
constraint
and
this
Does
field
least
>|||Kevin,
You can check for the same value I was suggesting as a default:
create table Kevin (
..
dateCol datetime CHECK (dateCol = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))
...
Steve
Kevin Sinclair wrote:

>I can't use a default because there is already a date in the field. What is
>supposed to happen is the teachers update this field when they do their
>daily attendance. However, at least 1 teacher every day, will input the
>wrong date. When this database was in access I used a validation rule on
>this field, I need a way to do the same thing in SQL Server 2000.
>Thanks.
>Kevin
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
>
>called
>
>constraint
>
>and
>
>this
>
>Does
>
>field
>
>least
>
>
>|||Oops. Ignore my previous message. The check constraint will fail
tomorrow if you update the row! You may need to use a trigger. What
you could do is put in the default and execute the update with:
UPDATE Attendance SET
this = whatever,
that = whatever,
dateCol = DEFAULT
WHERE
keyCol = theRowBeingEdited
SK
Kevin Sinclair wrote:

>I can't use a default because there is already a date in the field. What is
>supposed to happen is the teachers update this field when they do their
>daily attendance. However, at least 1 teacher every day, will input the
>wrong date. When this database was in access I used a validation rule on
>this field, I need a way to do the same thing in SQL Server 2000.
>Thanks.
>Kevin
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
>
>called
>
>constraint
>
>and
>
>this
>
>Does
>
>field
>
>least
>
>
>