July 3, 2012 at 9:42 am
Is there a way to make sure that in a bit column field that only one record for a specific set of criteria in the table is set to 1?
So for example I have a table that tracks claims
CREATE TABLE [dbo].[ClaimAccounting_Assignments](
[PracticeID] [int] NULL,
[ClaimID] [int] NULL,
[ClaimTransactionID] [int] NULL,
[InsurancePolicyID] [int] NULL,
[InsuranceCompanyPlanID] [int] NULL,
[PatientID] [int] NULL,
[LastAssignment] [bit] NULL,
[Status] [bit] NULL,
[PostingDate] [datetime] NOT NULL,
[EndPostingDate] [datetime] NULL,
[LastAssignmentOfEndPostingDate] [bit] NULL,
[EndClaimTransactionID] [int] NULL,
[DKPostingDateID] [int] NULL,
[DKEndPostingDateID] [int] NULL,
[RelativePrecedence] [int] NULL
) ON [PRIMARY]
So for every record that is related to the same claimid only one record can have the LastAssignment bit set to true. Is there a way for me to do that with a check constraint?
I couldn't think of any way.
July 3, 2012 at 9:45 am
pamozer (7/3/2012)
Is there a way to make sure that in a bit column field that only one record for a specific set of criteria in the table is set to 1?So for example I have a table that tracks claims
CREATE TABLE [dbo].[ClaimAccounting_Assignments](
[PracticeID] [int] NULL,
[ClaimID] [int] NULL,
[ClaimTransactionID] [int] NULL,
[InsurancePolicyID] [int] NULL,
[InsuranceCompanyPlanID] [int] NULL,
[PatientID] [int] NULL,
[LastAssignment] [bit] NULL,
[Status] [bit] NULL,
[PostingDate] [datetime] NOT NULL,
[EndPostingDate] [datetime] NULL,
[LastAssignmentOfEndPostingDate] [bit] NULL,
[EndClaimTransactionID] [int] NULL,
[DKPostingDateID] [int] NULL,
[DKEndPostingDateID] [int] NULL,
[RelativePrecedence] [int] NULL
) ON [PRIMARY]
So for every record that is related to the same claimid only one record can have the LastAssignment bit set to true. Is there a way for me to do that with a check constraint?
I couldn't think of any way.
I think it can be done. It would be a table level constraint. I may not have time before this evening to look deeper into this, but this should help you get started in your research.
July 3, 2012 at 10:20 am
Thanks. I'll see if I can figure something out with a table level constraint
July 3, 2012 at 12:19 pm
So I thought I had it figured out but it doesn't seem to be working.
I created the following function and then the constraint:
Alter FUNCTION FN_IsLastAssigned(@ClaimId INT)
RETURNS INT
AS
BEGIN
DECLARE @LastAssignment Int
SET @LastAssignment=(
SELECT COUNT(*)
FROM ClaimAccounting_Assignments AS caa
WHERE claimId=@ClaimID AND caa.LastAssignment=1)
RETURN @LastAssignment
END
ALTER TABLE ClaimAccounting_Assignments
WITH NOCHECK
ADD CONSTRAINT CK_Constraint_CAA_LastAssignments
CHECK ( dbo.FN_IsLastAssigned(ClaimId)=0)
But when I update the table it doesn't break when I make there be two records . Any thoughts?
Here is the table:
GO
/****** Object: Table [dbo].[ClaimAccounting_Assignments] Script Date: 07/03/2012 11:09:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ClaimAccounting_Assignments](
[PracticeID] [int] NULL,
[ClaimID] [int] NULL,
[ClaimTransactionID] [int] NULL,
[InsurancePolicyID] [int] NULL,
[InsuranceCompanyPlanID] [int] NULL,
[PatientID] [int] NULL,
[LastAssignment] [bit] NULL,
[Status] [bit] NULL,
[PostingDate] [datetime] NOT NULL,
[EndPostingDate] [datetime] NULL,
[LastAssignmentOfEndPostingDate] [bit] NULL,
[EndClaimTransactionID] [int] NULL,
[DKPostingDateID] [int] NULL,
[DKEndPostingDateID] [int] NULL,
[RelativePrecedence] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Index [CI_ClaimAccounting_Assignments] Script Date: 07/03/2012 11:09:26 ******/
CREATE CLUSTERED INDEX [CI_ClaimAccounting_Assignments] ON [dbo].[ClaimAccounting_Assignments]
(
[PracticeID] ASC,
[DKPostingDateID] DESC,
[DKEndPostingDateID] DESC,
[ClaimTransactionID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
/****** Object: Index [IX_ClaimAccounting_Assignments_ClaimID] Script Date: 07/03/2012 11:09:26 ******/
CREATE NONCLUSTERED INDEX [IX_ClaimAccounting_Assignments_ClaimID] ON [dbo].[ClaimAccounting_Assignments]
(
[ClaimID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
USE [superbill_1087_dev]
/****** Object: Index [IX_ClaimAccounting_Assignments_ClaimTransactionID] Script Date: 07/03/2012 11:09:26 ******/
CREATE NONCLUSTERED INDEX [IX_ClaimAccounting_Assignments_ClaimTransactionID] ON [dbo].[ClaimAccounting_Assignments]
(
[ClaimTransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
USE [superbill_1087_dev]
/****** Object: Index [IX_ClaimAccounting_Assignments_InsuranceCompanyPlanID] Script Date: 07/03/2012 11:09:26 ******/
CREATE NONCLUSTERED INDEX [IX_ClaimAccounting_Assignments_InsuranceCompanyPlanID] ON [dbo].[ClaimAccounting_Assignments]
(
[InsuranceCompanyPlanID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
/****** Object: Index [IX_ClaimAccounting_Assignments_InsurancePolicyID] Script Date: 07/03/2012 11:09:26 ******/
CREATE NONCLUSTERED INDEX [IX_ClaimAccounting_Assignments_InsurancePolicyID] ON [dbo].[ClaimAccounting_Assignments]
(
[InsurancePolicyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
USE [superbill_1087_dev]
/****** Object: Index [IX_ClaimAccounting_Assignments_PatientID] Script Date: 07/03/2012 11:09:26 ******/
CREATE NONCLUSTERED INDEX [IX_ClaimAccounting_Assignments_PatientID] ON [dbo].[ClaimAccounting_Assignments]
(
[PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
[IX_ClaimAccounting_Assignments_PracticeID_ClaimID_LastAssignment_INC_Status_InsuranceCompanyPlanID_InsurancePolicyID_PatientID] Script Date: 07/03/2012 11:09:26 ******/
CREATE NONCLUSTERED INDEX [IX_ClaimAccounting_Assignments_PracticeID_ClaimID_LastAssignment_INC_Status_InsuranceCompanyPlanID_InsurancePolicyID_PatientID] ON [dbo].[ClaimAccounting_Assignments]
(
[PracticeID] ASC,
[ClaimID] ASC,
[LastAssignment] ASC
)
INCLUDE ( [Status],
[InsuranceCompanyPlanID],
[InsurancePolicyID],
[PatientID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ClaimAccounting_Assignments] WITH NOCHECK ADD CONSTRAINT [CK_Constraint_CAA_LastAssignments] CHECK (([dbo].[FN_IsLastAssigned]([ClaimId])=(0)))
GO
ALTER TABLE [dbo].[ClaimAccounting_Assignments] CHECK CONSTRAINT [CK_Constraint_CAA_LastAssignments]
GO
ALTER TABLE [dbo].[ClaimAccounting_Assignments] ADD CONSTRAINT [DF_ClaimAccounting_Assignments_LastAssignment] DEFAULT (0) FOR [LastAssignment]
GO
ALTER TABLE [dbo].[ClaimAccounting_Assignments] ADD CONSTRAINT [DF_ClaimAccounting_Assigments_Status] DEFAULT (0) FOR [Status]
GO
ALTER TABLE [dbo].[ClaimAccounting_Assignments] ADD CONSTRAINT [DF_ClaimAccounting_Assignments_LastAssignmentOfEndPostingDate] DEFAULT ((0)) FOR [LastAssignmentOfEndPostingDate]
GO
Here's data to insert and the update statement that doesn't failINSERT INTO ClaimAccounting_Assignments
VALUES (1
, -- PracticeID
1191
, -- ClaimID
12345
, -- ClaimTransactionID
0
, -- InsurancePolicyID
0
, -- InsuranceCompanyPlanID
0
, -- PatientID
0
, -- LastAssignment
NULL
, -- Status
'2012-07-03 16:56:49'
, -- PostingDate
'2012-07-03 16:56:49'
, -- EndPostingDate
NULL
, -- LastAssignmentOfEndPostingDate
0
, -- EndClaimTransactionID
0
, -- DKPostingDateID
0
, -- DKEndPostingDateID
0 -- RelativePrecedence
),
VALUES (1
, -- PracticeID
1191
, -- ClaimID
12346
, -- ClaimTransactionID
0
, -- InsurancePolicyID
0
, -- InsuranceCompanyPlanID
0
, -- PatientID
0
, -- LastAssignment
1
, -- Status
'2012-07-04 16:56:49'
, -- PostingDate
'2012-07-04 16:56:49'
, -- EndPostingDate
NULL
, -- LastAssignmentOfEndPostingDate
0
, -- EndClaimTransactionID
0
, -- DKPostingDateID
0
, -- DKEndPostingDateID
0 -- RelativePrecedence
)
,
VALUES (1
, -- PracticeID
1191
, -- ClaimID
12347
, -- ClaimTransactionID
0
, -- InsurancePolicyID
0
, -- InsuranceCompanyPlanID
0
, -- PatientID
0
, -- LastAssignment
1
, -- Status
'2012-07-05 16:56:49'
, -- PostingDate
'2012-07-05 16:56:49'
, -- EndPostingDate
NULL
, -- LastAssignmentOfEndPostingDate
0
, -- EndClaimTransactionID
0
, -- DKPostingDateID
0
, -- DKEndPostingDateID
0 -- RelativePrecedence
)
BEGIN TRAN One
UPDATE ClaimAccounting_Assignments
SET LastAssignment=0
WHERE ClaimId=1191 AND ClaimAccounting_Assignments.PracticeID=1 AND ClaimAccounting_Assignments.ClaimTransactionID=12346
COMMIT
ROLLBACK
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply