primary keys

"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended."

can soemone explain to me why i have this kind of error?

i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no!

all the values from of application_input will be stored also in approve_overtime.

sometimes the datas can be stored.sometimes it cannot and produces an error!

what do u think?

hmmm pls help!

Check if you are wanting to insert duplicate id_no into approve_overtime ?

Preventing overlapping date records

Hi,
I have a SQL Server 2000 table which holds timesheet data. Each timesheet is
made up of one or more timesheet elements which would e.g. correspond to the
number of hours worked by an employee in any given day. I'm looking for a
way to ensure that employees do not enter duplicate or overlapping records.
E.g. let's say tblTimesheet holds the following records:
dtmStart dtmEnd
-- --
2003-09-15 09:15:00 2003-09-15 17:00:00
2003-09-16 09:15:00 2003-09-16 17:00:00
I need some help with a stored procedure which will accept a starting
datetime and an ending datetime as parameters and tell me whether that would
overlap in any way with any existing record. The two parameter dates will
already have been validated before being passed to the SP, so I know that
they are valid dates and that the ending datetime will be later than the
starting date time.
So,
CREATE PROCEDURE usp_ValidateTimesheetEntry
@.pdtmStart datetime,
@.pdtmEnd datetime
SELECT COUNT(*) FROM tblTimesheet
WHERE ...-- this is the bit I'm stuck on
Examples
=======
If @.pdtmStart is 2003-09-17 09:15:00 and @.pdtmEnd is 2003-09-17 17:00:00
this is valid.
If @.pdtmStart is 2003-09-16 18:30:00 and @.pdtmEnd is 2003-09-16 23:50:00
this is valid.
If @.pdtmStart is 2003-09-15 01:00:00 and @.pdtmEnd is 2003-09-15 09:30:00
this is invalid because it overlaps with the first record.
If @.pdtmStart is 2003-09-16 11:00:00 and @.pdtmEnd is 2003-09-16 16:30:00
this is invalid because it overlaps (or rather is totally contained within)
the second record.
Any assistance gratefully received.
MarkYou can use a combination of constraints and a trigger:
CREATE TABLE Timesheet (employee INTEGER NOT NULL /* REFERENCES
Employees (employee) */, dtmstart DATETIME NOT NULL, dtmend DATETIME
NOT NULL, CHECK (dtmstart<dtmend), UNIQUE (employee,dtmstart))
GO
CREATE TRIGGER trg_timesheet_no_overlap
ON Timesheet
FOR INSERT, UPDATE
AS
IF EXISTS
(SELECT * FROM inserted I
JOIN Timesheet T ON
I.employee = T.employee
AND I.dtmend > T.dtmstart
AND I.dtmstart < T.dtmend
AND I.dtmstart <> T.dtmstart)
BEGIN
RAISERROR ('Overlapping times not allowed', 16, 1)
ROLLBACK TRANSACTION
END
Notice that this will allow double shifts, i.e. 1st Shift End = 2nd
Shift Start. If you don't want that just change > and < to >= and <=.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110719703.701430.301990@.f14g2000cwb.googlegroups.com...

> You can use a combination of constraints and a trigger:
Perfect! Thanks very much.

Preventing overlapping data entry

Hello Everyone,

I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved.

The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID.

Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed.

After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments.

Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know.

Thanks in advance,

Brian

This query should give you the timeIDs that have not been scheduled:

SELECT t.pkTimeIDFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT c.timeIDFROM tblCalendar cWHERE c.date ='4/20/2007' )

Replace the date with the date the user has selected.|||

This query leaves out the prior and next time slots to avoid overlapping a one hour appointment.

SELECT t.pkTimeIDFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT unavailableTimes.timeIDFROM (SELECT c.timeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDUNION SELECT t1.pkTimeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDLEFTJOIN tblTime t1ON t1.time =SUBSTRING(CONVERT(VARCHAR(20),DATEADD(n, -30, c.date +' ' + t.time)), 14, 7)UNION SELECT t2.pkTimeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDLEFTJOIN tblTime t2ON t2.time =SUBSTRING(CONVERT(VARCHAR(20),DATEADD(n, 30, c.date +' ' + t.time)), 14, 7) ) unavailableTimesWHERE unavailableTimes.date ='4/20/2007' )

This query is not optimal but you get the idea. Again, replace the date with the date the user has selected. You may also need to tweak the SUBSTRING params based on the format of your DATETIMEs.|||

Thank you so much for your help. I think I was a little to vague on my description but this solution helped me get a solution that worked for me. I needed to check for the previous and next pkTimeID value to eliminate not the actual datetime values. Just to keep this short my statement is as follows:

SELECT t.pkTimeID, t.timeFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT c.timeID - 1FROM tblCalendar cUNIONSELECT c1.timeIDFROM tblCalendar c1UNIONSELECT c2.timeID + 1FROM tblCalendar c2WHERE (c2.[date] ='4/20/2007'))

Thank you again for your help, and if you have any suggestions on how I might improve what I did come up with I would appreciate it. This is my first "real" project in the work place and I would like to learn the best way to approach a problem where ever I can, so that I will not have to learn these things later.

Brian