Transact SQL :: Check Constraint With UDF To Prevent Overlapping Dates?
Oct 6, 2015
I am trying to create a check constraint that prevents entry of a date range that overlaps any other date range in the table. The below example script results in the error message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap". what I am doing wrong?
CREATE TABLE t1 (d1 date,d2 date)
go
create function dbo.dt_overlap (@d1 date, @d2 date) RETURNS BIT
AS
BEGIN
IF EXISTS (SELECT 1 from t1 where d1 between @d1 and @d2 or d2 between @d1 and @d2)
RETURN 1
RETURN 0
END
go
alter table t1 add constraint ck_dt_overlap CHECK (dbo.dt_overlap(d1,d2)=0)
go
insert into t1 values ('2015-01-01','2015-02-01')
insert into t1 values ('2015-01-15','2015-02-15')
--BOTH inserts result in the following message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap".
Hi,I need to enforce that a table does not have "duplicates" for aspecific status type in the table.If the column "STATUS" = 2, then there can not be more than one rowwith a specific "ID" column.I can not use a unique key constraint because duplicate values for thiscombo of columns is valid for the status = 1.Just when the status = 2, there can not be any other rows with the sameID and status = 2.Any ideas?-Paul
IF OBJECT_ID('tempdb..#OverLappingDates') IS NOT NULL DROP TABLE #OverLappingDates CREATE TABLE #OverLappingDates ( MemberID Varchar (50), ClaimID Varchar(50), StartDate DateTime, EndDate DateTime )
[Code] ....
I need to select only records where dates are over lapping for the same memberid...For this scenario i need an output First 2 records (MemberID 1 has 2 claiims overlapping dates).
And I would like to produce the following outcome to the same table (using update statement): As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.
The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,1) INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2) INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,3)
SELECT * FROM PRODUCT_SALES
/* SalesmanID is reference key from Sales Master and ProductID is reference key from Product Master. How should i restrict user through DB with any constraint check, if user tries to enter
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2),
It should throw error , if possible user defined message would be more useful.
Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.
All well and good.But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.
My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?
I am trying to either write a trigger or a check constraint to preventduplicates in my table. There are two columns I need to look at for theduplicates and only one combo value for both columns is allowed in thetable. For e.g.Column Serial can have only one '123456' value with testresult value as'PASS'. This serial can be in the table many times with any other comboso for e.g.The table could contain 100 entries for serial column value '123456'with testresult value 'FAIL', 'PENDING' etc.** TESTED SCHEMA BELOW **-- create tableCREATE TABLE bstresult(ID int IDENTITY (1, 1) NOT NULL ,serial char (10) NULL ,testresult char (10) NULL)-- Insert valid valuesinsert into bstresult values ('123456','PASS')insert into bstresult values ('123456','FAIL')insert into bstresult values ('123456','FAIL')insert into bstresult values ('123456','PENDING')-- insert invalid value this should failinsert into bstresult values ('123456','PASS')If I simply create a unique constraint on both columns it will notallow the FAIL combo or PENDING combo with the same serial which I needto allow.Appreciate your help.
So What I am trying to accomplish is sum up overlapping time ranges while also keeping Unique data rows within the table. So If a data row is unique meaning it is NOT within a overlapping group of data rows then I want to just insert it into a "final table", but if the data rows are overlapping then I want to grab the min(timestart) and the max(timestop) and the PKID of the data row with the max(timestop) within the overlapping group.
I accomplish this task using nested cursors, however When I place my method into a trigger which runs on INSERT, then my trigger never runs and nothing happens. Is there a way to accomplish this without using cursors. I have placed my cursor method into the sql fiddle to be inspected.
create table temp1 ( pkid int, line int, dateentry date, timestart time, timestop time )
I have a table with multiple rows per staff person. Each of these rows has staff_id, start_date, and end_date. Per staff, if any start_date comes between the start_date and end_date of a different row, or if any end_date comes between the start_date and end_date of a different row, then I have to flag these records as being identical.
How can I do this? I have tried doing a Cross Apply because I thought that would do Cartesian product (comparing every row), and I've also tried temp tables. But I haven't gotten either of these to work. Here is some dummy data:
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#staff_records') ) DROP TABLE #staff_records; create table #staff_records ( staff_id varchar(max),
Hi I was wodering how to add an OR statment right in the Check Constraint expression. This is what I am starting with in the database ([zip] like '[0-9][0-9][0-9][0-9][0-9]') and what I want well not exact but this would answer my question ([zip] like '[0-9][0-9][0-9][0-9][0-9] || [A-Z][A-Z][A-Z][A-Z][A-Z]') Thanks for any help
Hi, i want to put a check constraint on one of my tables to make sure that a member doesn't get loaded into the same usergroup twice. Could anyone please help me with this.
Here is some sample code:
Code Block
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), MemberID INT, ClientID INT, UsergroupID INT) INSERT INTO @MyTable VALUES (123456, 211, 3054) -- Member gets loaded twice INSERT INTO @MyTable VALUES (123456, 211, 3054) INSERT INTO @MyTable VALUES (123456, 211, 3055) -- Same Member as the top but into ---- a different usergroup which is correct INSERT INTO @MyTable VALUES (215489, 376, 2017) INSERT INTO @MyTable VALUES (987512, 345, 2237) INSERT INTO @MyTable VALUES (235479, 333, 1158) INSERT INTO @MyTable VALUES (354985, 333, 1158)
SELECT * FROM @MyTable
The problem i have is that if a member is loaded into the same usergroup twice then the sites crash on the web applications and the member can't get into his profile page.Unfortunately i don't know which one of the 1000 stored procedures are used to access this table so that i could just change the query to SELECT TOP 1.
However i think that it will be better practive to just put a check contraint on the table and avoide the whole problem.
However 1 member can belong to multiple usergroups.
In our database we have an indexed field that is using unique values, so in the index we turned on the unique property. Now with some changes we made in the application this value can be Null (could not be Null in the past). When we have more as 1 value having value Null we get an exception of unique key violation.
Therefor we want to make an check constraint the checks if the value allready exists when the value is not Null.
Hi, I am trying to enforce a rule that only one record in a table can have status 'Active'. I'd like to do this with CHECK constraint rather than trigger. I know SQL server doesn't support subqueries such as : alter table add constraint chk_OnlyOneActive CHECK (1=(select count(*) from mytable where status='Active')) Is there any workaround for this? P.S. I tried using UDF like this: alter table add constraint chk_OnlyOneActive CHECK (dbo.NumActiveRecords()=1) and it works for insert - I am not able to insert a second "Active" record, but for some reason doesn't work for update - I am able to update existing record to status "Active" and end up with two or more Active records in the table. Thank you.
I am creating a check constraint on a field (GRID_NBR) for values between 1 & 99. I am a little confused on creating the expression for it (Books online is vague).
Can I use the following expression: GRID_NBR BETWEEN 1 AND 99
Or do I have to use: GRID_NBR > 0 AND GRID_NBR < 100
I have a table with 3 columns in it. The ID column has a datetime data type.
Does anyone know how to apply a check constraint on this field so that the same month cannot be entered twice.
For example the field has the following data in the field 26/04/2008 27/05/2008 26/06/2008 25/07/2008
A user then tries to enter the value 20/05/2008, I would like the check constraint to block this value being entered due to the fact that a item with the same month value has already been entered.
I have created a table which contains date periods. It has 3 fields all with DateTime data types. These fields being ID, BeginDate, and EndDate. The ID field will hold a date indicating what month the reord is for, ie 01/03/2007 being March or 05/04/2007 being April. The BeginDate will contain the beginnig date for the financial month and EndDate for the financial month.
I am trying to apply a check constraint on the ID field so that combaination of a month and year can only be entered once, ie. if 01/03/2007 already exists in the table then 03/03/2007 cannot.
I need to create a check constraint for an email column/field, where the field must contain an "@" symbol. Does sql (oracle or SQL in general) let you do this.
1. Tried myself: ALTER TABLE Q_Customer ADD CONSTRAINT Q_chk_Cus_email CHECK (Cus_email LIKE '%@%');
I want to incorporate a Check constraint within a trigger, based on this but im struggling with the coding.Assuming that is an Alphanumeric field you should be checking what value is in the alphanumeric column from inserted, comparing it with whatever is in the AMGR_User_Fields_Tbl to see if there’s a duplicate and then raising the error...This is my Trigger:
ALTER TRIGGER [dbo].[DUPLICATES] ON [dbo].[AMGR_User_Fields_Tbl]
FOR INSERT, UPDATE AS DECLARE @Alphanumericcol VARCHAR (750)
-- This trigger has been created to check that duplicate rows are not inserted into table.
-- Check if row exists SELECT @Alphanumericcol FROM Inserted i, AMGR_User_Fields_Tbl t
I am having some difficulties to find the documentation relative to the syntax of CHECK and CONTRAINT in MS SQL Server 2005. Can someone points me toward a reference guide?
Thanks in advance, Joannès http://www.peoplewords.com
