Check Constraint

I'm having a little trouble enforcing a unique record rule. The problem is
a
DateTime field is used in the unique value, but I only need to the DatePart
(not
the time), otherwise I would use a standard unique constraint.
I need the combination of ResID,RxDayID,RxTimeID, and Just The Date of
DispDateTime to be unique.
The following works for inserts but not updates if a record already exists.
It
does work if there are 2 matching records for an update. I'd rather have th
e
back-end enforce the rules, but if I must I'll build it into the application
.
Is there a function or a method I'm over-looking?
ALTER TABLE [dbo].[ResRxDispensed] ADD
CONSTRAINT [CK_ResRxDispensed_Unique]
CHECK ([dbo].[DispRecordUnique_FN](
[ResID],
[RxDayID],
[RxDoseTimeID],
[DispDateTime]) = 'Y')
Create Function [dbo].[DispRecordUnique_FN](
@.ResID Int,
@.RxDayID Int,
@.RxTimeID Int,
@.ADate DateTime)
Returns VarChar(1)
Begin
Declare @.TheDate DateTime, @.Rtn Varchar(1),@.Cnt Int
Set @.TheDate = Cast(Cast(@.ADate as Char(11)) As DateTime)
Set @.Rtn = 'N'
Select @.Cnt = Count(*)
From ResRxDispensed
Where ResID = @.ResID
And RxDayID = @.RxDayID
And RxDoseTimeID = @.RxTimeID
And (DispDateTime >= @.TheDate And DispDateTime <= (@.TheDate+1))
If (@.Cnt <= 1)
Begin
Set @.Rtn = 'Y'
End
Return @.Rtn
End
TIA,
-Steve-You've overcomplicated the solution IMHO:
1) create a computed column:
alter table dbo.ResRxDispensed
add DispDate as convert(char(8), DispDateTime, 112)
go
2) create the unique constraint:
alter table dbo.ResRxDispensed
add constraint <constraint name>
unique (ResID, RxDayID, RxTimeID, DispDate)
go
If this does not help, please post proper DDL and maybe sample data.
ML
http://milambda.blogspot.com/|||Experiment to see if the year(), month(), day() or datediff() functions
would work here.
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:%23kFjHVZWGHA.2180@.TK2MSFTNGP02.phx.gbl...
> I'm having a little trouble enforcing a unique record rule. The problem
> is a DateTime field is used in the unique value, but I only need to the
> DatePart (not the time), otherwise I would use a standard unique
> constraint.
> I need the combination of ResID,RxDayID,RxTimeID, and Just The Date of
> DispDateTime to be unique.
> The following works for inserts but not updates if a record already
> exists. It does work if there are 2 matching records for an update. I'd
> rather have the back-end enforce the rules, but if I must I'll build it
> into the application. Is there a function or a method I'm over-looking?
> ALTER TABLE [dbo].[ResRxDispensed] ADD
> CONSTRAINT [CK_ResRxDispensed_Unique]
> CHECK ([dbo].[DispRecordUnique_FN](
> [ResID],
> [RxDayID],
> [RxDoseTimeID],
> [DispDateTime]) = 'Y')
>
> Create Function [dbo].[DispRecordUnique_FN](
> @.ResID Int,
> @.RxDayID Int,
> @.RxTimeID Int,
> @.ADate DateTime)
> Returns VarChar(1)
> Begin
> Declare @.TheDate DateTime, @.Rtn Varchar(1),@.Cnt Int
> Set @.TheDate = Cast(Cast(@.ADate as Char(11)) As DateTime)
> Set @.Rtn = 'N'
> Select @.Cnt = Count(*)
> From ResRxDispensed
> Where ResID = @.ResID
> And RxDayID = @.RxDayID
> And RxDoseTimeID = @.RxTimeID
> And (DispDateTime >= @.TheDate And DispDateTime <= (@.TheDate+1))
> If (@.Cnt <= 1)
> Begin
> Set @.Rtn = 'Y'
> End
> Return @.Rtn
> End
> TIA,
> -Steve-
>|||Thanks.
I tried your suggestion but it apparently doesn't work when you use a comput
ed
column in a constraint. When I attempt to add a record I get the errror:
[INSERT failed because the following SET options have incorrect settings:
'ARITHABORT']
Using SET ARITHABORT ON before the insert/update works, but it seems it must
be
issued before each update/insert statement.

> If this does not help, please post proper DDL and maybe sample data.
CREATE TABLE [dbo].[ResRxDispensed] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ResID] [int] NULL ,
[ResRxID] [int] NULL ,
[RxDayID] [int] NULL ,
[RxDoseTimeID] [int] NULL ,
[DispDateTime] [datetime] NULL ,
[Created] [datetime] NULL ,
[StaffID] [int] NULL ,
[DispDate] AS (convert(char(8),[DispDateTime],112))
) ON [PRIMARY]
Insert Into ResRxDispensed
([ResID],[ResRxID],[RxDayID],[RxDoseTime
ID],[DispDateTime])
Values
(1,1,1,1,{ts '2006-04-06 09:30:00'}) ;
The insert should fail if another record has
ResID = 1
RxDayID = 1
RxDoseTimeID = 1
DispDateTime = 2006-04-06 (with any time factor)
Updates should also fail if the update changes the record to match another
record with the criteria.
-Steve-|||
"JT" <someone@.microsoft.com> wrote in message
news:%23sosFSaWGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Experiment to see if the year(), month(), day() or datediff() functions wo
uld
> work here.
I'm not sure I understand. Experiment how? The function
DispRecordUnique_FN()works, but using it in a Check constraint only works on
Inserts, not updates.
-Steve-|||This worked fine for me on SQL 2000.
CREATE TABLE [dbo].[TestTable] (
[ResID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RxDayID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RxDoseTimeID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DispDateTime] [datetime] NOT NULL ,
[OtherData] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DispDate] AS (convert(char(8),[DispDateTime],112))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestTable] ADD
CONSTRAINT [IX_TestTable] UNIQUE NONCLUSTERED
(
[ResID],
[RxDoseTimeID],
[RxDayID],
[DispDate]
) ON [PRIMARY]
GO
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:ep7wCVaWGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Thanks.
> I tried your suggestion but it apparently doesn't work when you use a
computed
> column in a constraint. When I attempt to add a record I get the errror:
> [INSERT failed because the following SET options have incorrect settings:
> 'ARITHABORT']
> Using SET ARITHABORT ON before the insert/update works, but it seems it
must be
> issued before each update/insert statement.
>
> CREATE TABLE [dbo].[ResRxDispensed] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ResID] [int] NULL ,
> [ResRxID] [int] NULL ,
> [RxDayID] [int] NULL ,
> [RxDoseTimeID] [int] NULL ,
> [DispDateTime] [datetime] NULL ,
> [Created] [datetime] NULL ,
> [StaffID] [int] NULL ,
> [DispDate] AS (convert(char(8),[DispDateTime],112))
> ) ON [PRIMARY]
>
> Insert Into ResRxDispensed
> ([ResID],[ResRxID],[RxDayID],[RxDoseTime
ID],[DispDateTime])
> Values
> (1,1,1,1,{ts '2006-04-06 09:30:00'}) ;
> The insert should fail if another record has
> ResID = 1
> RxDayID = 1
> RxDoseTimeID = 1
> DispDateTime = 2006-04-06 (with any time factor)
> Updates should also fail if the update changes the record to match another
> record with the criteria.
> -Steve-
>
>|||> This worked fine for me on SQL 2000.
Hi Jim,
Thanks. I'm using SQL 2000, but I'm still getting the same error.
Here's the scripts for creating everything I'm using.
CREATE TABLE [dbo].[ResRxDispensed] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ResID] [int] NULL ,
[ResRxID] [int] NULL ,
[RxDayID] [int] NULL ,
[RxDoseTimeID] [int] NULL ,
[DispDateTime] [datetime] NOT NULL ,
[Created] [datetime] NULL ,
[StaffID] [int] NULL ,
[DispDate] AS (convert(char(8),[DispDateTime],112))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ResRxDispensed] WITH NOCHECK ADD
CONSTRAINT [PK_ResRxDispensed] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ResRxDispensed] ADD
CONSTRAINT [DF_ResRxDispensed_DispDateTime] DEFAULT (getdate()) FOR
[DispDateTime],
CONSTRAINT [DF_ResRxDispensed_Created] DEFAULT (getdate()) FOR [Created],
CONSTRAINT [IX_ResRxDispensed] UNIQUE NONCLUSTERED
(
[ResID],
[RxDayID],
[RxDoseTimeID],
[DispDate]
) ON [PRIMARY]
GO
CREATE INDEX [ResRxDispensed_ResID] ON [dbo].[ResRxDispensed]([ResID]) ON
[PRIMARY]
GO
CREATE INDEX [ResRxDispensed_ResRxID] ON [dbo].[ResRxDispensed]([ResRxID]) ON
[PRIMARY]
GO
CREATE INDEX [ResRxDispensed_RxDayID] ON [dbo].[ResRxDispensed]([RxDayID]) ON
[PRIMARY]
GO
CREATE INDEX [ResRxDispensed_RxDoseTimeID] ON
[dbo].[ResRxDispensed]([RxDoseTimeID]) ON [PRIMARY]
GO
-Steve-|||Is there a reason why you can't add this SET option to your stored procedure
s?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:%23Set$paWGHA.2080@.TK2MSFTNGP05.phx.gbl...
> Hi Jim,
> Thanks. I'm using SQL 2000, but I'm still getting the same error.
> Here's the scripts for creating everything I'm using.
> CREATE TABLE [dbo].[ResRxDispensed] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ResID] [int] NULL ,
> [ResRxID] [int] NULL ,
> [RxDayID] [int] NULL ,
> [RxDoseTimeID] [int] NULL ,
> [DispDateTime] [datetime] NOT NULL ,
> [Created] [datetime] NULL ,
> [StaffID] [int] NULL ,
> [DispDate] AS (convert(char(8),[DispDateTime],112))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ResRxDispensed] WITH NOCHECK ADD
> CONSTRAINT [PK_ResRxDispensed] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ResRxDispensed] ADD
> CONSTRAINT [DF_ResRxDispensed_DispDateTime] DEFAULT (getdate()) FOR [DispDateTime],
> CONSTRAINT [DF_ResRxDispensed_Created] DEFAULT (getdate()) FOR [Created],
> CONSTRAINT [IX_ResRxDispensed] UNIQUE NONCLUSTERED
> (
> [ResID],
> [RxDayID],
> [RxDoseTimeID],
> [DispDate]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [ResRxDispensed_ResID] ON [dbo].[ResRxDispensed]([ResID]) ON [PRIMARY]
> GO
> CREATE INDEX [ResRxDispensed_ResRxID] ON [dbo].[ResRxDispensed]([ResRxID]) ON [PRIMARY]
> GO
> CREATE INDEX [ResRxDispensed_RxDayID] ON [dbo].[ResRxDispensed]([RxDayID]) ON [PRIMARY]
> GO
> CREATE INDEX [ResRxDispensed_RxDoseTimeID] ON [dbo].[ResRxDispensed]([RxDoseTimeID]) ON [PRIMARY]
> GO
>
> -Steve-
>|||"Tibor Karaszi" wrote
> Is there a reason why you can't add this SET option to your stored procedures?[/co
lor]
I suppose not. I just wanted something that didn't require such special
handling for updates in case I needed to do some manual repair/entry outside
of
the application.
-Steve-|||"Tibor Karaszi" wrote:
> Is there a reason why you can't add this SET option to your stored procedures?[/co
lor]
This procedure, when used, produces the same error.
[INSERT failed because the following SET options have incorrect settings:
'ARITHABORT']
I'm at a loss how to proceed...
-Steve-
Exec NewResRxDispensed_SP 1,1,1,1,{ts '2006-05-13 09:30:00'},24
Create Procedure [dbo].[NewResRxDispensed_SP]
@.ResID int,
@.ResRxID int,
@.RxDayID int,
@.RxDoseTimeID int,
@.DispDateTime datetime,
@.StaffID int
As
SET ARITHABORT ON
Insert Into [ResRxDispensed]
([ResID],
[ResRxID],
[RxDayID],
[RxDoseTimeID],
[DispDateTime],
[StaffID])
Values
(@.ResID,
@.ResRxID,
@.RxDayID,
@.RxDoseTimeID,
@.DispDateTime,
@.StaffID)
/*** Return New Int ID [ID] ***/
Select SCOPE_IDENTITY() As NewID

Charting Trouble

Well, this is what is happening.
I am getting my data from a stored procedure, and for this particular chart
am need to use 4 different data fields, 'AverageFillRate',
'AverageFillRateTarget', 'Years', and 'Months'. The years need to correspond
to the averagefillrate, so for example, between 2004 and 2006 I will have 3
fill rates, but the averagefillratetarget is constant among all years.
This is the way the legend appears now:
-- 2004 Average Fill Rate
-- 2004 Average Fill Rate Target
-- 2005 Average Fill Rate
-- 2005 Average Fill Rate Target
-- 2006 Average Fill Rate
-- 2006 Average Fill Rate Target
I need it to be:
-- Average Fill Rate Target
-- 2004 Average Fill Rate
-- 2005 Average Fill Rate
-- 2006 Average Fill Rate
..... etc
Since the target is constant among all years, it does not need to correspond
to any year field, and should be a straight line across the chart. Then I
should have another three different lines on the chart corresponding to the
years selected. But I have 6 lines, because I am getting a target line for
each year as well, this is a mess.
Right now I have 'AverageFillRateTarget' and 'AverageFillRate' fields
dropped into the data field area, 'Months' dropped into the category field
area and 'Years' dropped into the series field area of the chart.
Any suggestions, and does this help any?
ThanksPlease check my response on your other thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=1ca451e6-5cc2-43fc-9fd9-bb51e1a3967d&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:5B978BF7-4D62-4D5C-9355-163AB6F7FCF3@.microsoft.com...
> Well, this is what is happening.
> I am getting my data from a stored procedure, and for this particular
chart
> am need to use 4 different data fields, 'AverageFillRate',
> 'AverageFillRateTarget', 'Years', and 'Months'. The years need to
correspond
> to the averagefillrate, so for example, between 2004 and 2006 I will have
3
> fill rates, but the averagefillratetarget is constant among all years.
> This is the way the legend appears now:
> -- 2004 Average Fill Rate
> -- 2004 Average Fill Rate Target
> -- 2005 Average Fill Rate
> -- 2005 Average Fill Rate Target
> -- 2006 Average Fill Rate
> -- 2006 Average Fill Rate Target
>
> I need it to be:
> -- Average Fill Rate Target
> -- 2004 Average Fill Rate
> -- 2005 Average Fill Rate
> -- 2006 Average Fill Rate
> ..... etc
>
> Since the target is constant among all years, it does not need to
correspond
> to any year field, and should be a straight line across the chart. Then I
> should have another three different lines on the chart corresponding to
the
> years selected. But I have 6 lines, because I am getting a target line for
> each year as well, this is a mess.
> Right now I have 'AverageFillRateTarget' and 'AverageFillRate' fields
> dropped into the data field area, 'Months' dropped into the category field
> area and 'Years' dropped into the series field area of the chart.
> Any suggestions, and does this help any?
> Thanks