June 5, 2008 at 1:08 pm
I have the following table that I need to search before entering any new records. I have created a function that will check if the prvided TimeStart and TimeEnd falls / overlaps with any existing records with matching ProjectID and TImeEntryUserID.
Currently I have the function just checking the STartTime. No matter what I enter for the start time it always returns 1.
Any ideas?
CREATE TABLE [TimeEntry] (
[TimeEntryID] [int] IDENTITY (1, 1) NOT NULL ,
[ProjectID] [int] NOT NULL ,
[WorkAreaPayItemID] [int] NOT NULL ,
[TimeEntryStart] [datetime] NOT NULL ,
[TimeEntryEnd] [datetime] NOT NULL ,
[TimeEntryCreated] [datetime] NOT NULL CONSTRAINT [DF_TimeEntry_TimeEntryCreated] DEFAULT (getdate()),
[TimeEntryDuration] [decimal](5, 2) NOT NULL ,
[TimeEntryDescription] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TimeEntryDate] [datetime] NOT NULL ,
[TimeEntryCreatorID] [int] NOT NULL ,
[TimeEntryUserID] [int] NOT NULL ,
[OriginatingTimeEntryID] [int] NULL ,
[isOT] [bit] NULL CONSTRAINT [DF_TimeEntry_isOT] DEFAULT (0),
[OTComments] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [int] NOT NULL CONSTRAINT [DF_TimeEntry_Status] DEFAULT (5),
[OTWorkAreaPayItemID] [int] NULL ,
[PlantID] [int] NULL ,
[WorkDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Materials] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last_Mod_UserID] [int] NULL ,
[Last_Mod_Date] [datetime] NOT NULL CONSTRAINT [DF_TimeEntry_Last_Mod_Date] DEFAULT (getdate()),
CONSTRAINT [PK_TimeEntry] PRIMARY KEY CLUSTERED
(
[TimeEntryID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_TimeEntry] UNIQUE NONCLUSTERED
(
[ProjectID],
[WorkAreaPayItemID],
[TimeEntryStart],
[TimeEntryUserID],
[TimeEntryDuration],
[OriginatingTimeEntryID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_TimeEntry_TimeEntry] FOREIGN KEY
(
[OriginatingTimeEntryID]
) REFERENCES [TimeEntry] (
[TimeEntryID]
)
) ON [PRIMARY]
GO
Alter FUNCTION [dbo].[f_TimeEntryOverlap]
(
@ProjectIDint,
@TimeEntryStartDateTime,
@TimeEntryEndDateTIme,
@TimeEntryUserIDInt
)
RETURNS Bit
AS
BEGIN
Declare @Resultbit;
if Exists (Select 1 from TimeEntry where ProjectID = @ProjectID
and TimeENtryUserID = @TimeEntryUserID
and @TimeEntryStart between TimeEntryStart and TimeEntryEnd)
BEGIN
Set @Result = 1;
END
Return @Result;
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
set quoted_identifier off
set identity_insert TimeEntry on
if not exists(select * from TimeEntry where TimeEntryID=1384)
Insert into TimeEntry(TimeEntryID,ProjectID,WorkAreaPayItemID,TimeEntryStart,TimeEntryEnd,TimeEntryCreated,TimeEntryDuration,TimeEntryDescription,TimeEntryDate,TimeEntryCreatorID,TimeEntryUserID,OriginatingTimeEntryID,isOT,OTComments,Status,OTWorkAreaPayItemID,PlantID,WorkDesc,Materials,Last_Mod_UserID,Last_Mod_Date)
Values ( 1384,35,70,"Apr 14 2008 2:30:00:000AM","Apr 14 2008 7:30:00:000AM","Apr 14 2008 9:09:13:273AM",5.00,"fefefef","Apr 14 2008 9:09:13:460AM",1024,1035,null,0,null,5,null,0,"","efef",0,"Apr 14 2008 9:09:13:273AM")
set identity_insert TimeEntry off
set identity_insert TimeEntry on
if not exists(select * from TimeEntry where TimeEntryID=1385)
Insert into TimeEntry(TimeEntryID,ProjectID,WorkAreaPayItemID,TimeEntryStart,TimeEntryEnd,TimeEntryCreated,TimeEntryDuration,TimeEntryDescription,TimeEntryDate,TimeEntryCreatorID,TimeEntryUserID,OriginatingTimeEntryID,isOT,OTComments,Status,OTWorkAreaPayItemID,PlantID,WorkDesc,Materials,Last_Mod_UserID,Last_Mod_Date)
Values ( 1385,35,70,"May 5 2008 7:00:00:000AM","May 5 2008 5:00:00:000PM","May 7 2008 2:59:39:150PM",10.00,"THIS IS A TEST","May 7 2008 3:11:27:970PM",1024,1035,null,0,null,5,null,0,"","",0,"May 7 2008 2:59:39:150PM")
set identity_insert TimeEntry off
set identity_insert TimeEntry on
if not exists(select * from TimeEntry where TimeEntryID=1386)
Insert into TimeEntry(TimeEntryID,ProjectID,WorkAreaPayItemID,TimeEntryStart,TimeEntryEnd,TimeEntryCreated,TimeEntryDuration,TimeEntryDescription,TimeEntryDate,TimeEntryCreatorID,TimeEntryUserID,OriginatingTimeEntryID,isOT,OTComments,Status,OTWorkAreaPayItemID,PlantID,WorkDesc,Materials,Last_Mod_UserID,Last_Mod_Date)
Values ( 1386,35,70,"May 6 2008 7:00:00:000AM","May 6 2008 3:30:00:000PM","May 7 2008 3:00:24:870PM",8.50,"fefefef","May 7 2008 3:11:27:987PM",1024,1035,null,0,null,5,null,0,"","",0,"May 7 2008 3:00:24:870PM")
set identity_insert TimeEntry off
set identity_insert TimeEntry on
if not exists(select * from TimeEntry where TimeEntryID=1387)
Insert into TimeEntry(TimeEntryID,ProjectID,WorkAreaPayItemID,TimeEntryStart,TimeEntryEnd,TimeEntryCreated,TimeEntryDuration,TimeEntryDescription,TimeEntryDate,TimeEntryCreatorID,TimeEntryUserID,OriginatingTimeEntryID,isOT,OTComments,Status,OTWorkAreaPayItemID,PlantID,WorkDesc,Materials,Last_Mod_UserID,Last_Mod_Date)
Values ( 1387,35,70,"May 7 2008 7:00:00:000AM","May 7 2008 3:30:00:000PM","May 7 2008 3:01:32:123PM",8.50,"vdvdvdfvdvd","May 7 2008 3:11:27:987PM",1024,1035,null,0,null,5,null,0,"","",0,"May 7 2008 3:01:32:123PM")
set identity_insert TimeEntry off
set identity_insert TimeEntry on
if not exists(select * from TimeEntry where TimeEntryID=1388)
Insert into TimeEntry(TimeEntryID,ProjectID,WorkAreaPayItemID,TimeEntryStart,TimeEntryEnd,TimeEntryCreated,TimeEntryDuration,TimeEntryDescription,TimeEntryDate,TimeEntryCreatorID,TimeEntryUserID,OriginatingTimeEntryID,isOT,OTComments,Status,OTWorkAreaPayItemID,PlantID,WorkDesc,Materials,Last_Mod_UserID,Last_Mod_Date)
Values ( 1388,35,70,"May 8 2008 7:00:00:000AM","May 8 2008 4:00:00:000PM","May 7 2008 3:02:18:907PM",9.00,"efefefef","May 7 2008 3:11:28:000PM",1024,1035,null,0,null,5,null,0,"","",0,"May 7 2008 3:02:18:907PM")
set identity_insert TimeEntry off
set identity_insert TimeEntry on
if not exists(select * from TimeEntry where TimeEntryID=1393)
Insert into TimeEntry(TimeEntryID,ProjectID,WorkAreaPayItemID,TimeEntryStart,TimeEntryEnd,TimeEntryCreated,TimeEntryDuration,TimeEntryDescription,TimeEntryDate,TimeEntryCreatorID,TimeEntryUserID,OriginatingTimeEntryID,isOT,OTComments,Status,OTWorkAreaPayItemID,PlantID,WorkDesc,Materials,Last_Mod_UserID,Last_Mod_Date)
Values ( 1393,35,70,"May 9 2008 7:00:00:000AM","May 9 2008 3:00:00:000PM","May 7 2008 3:11:27:923PM",4.00,"ncncnbxcn","May 7 2008 3:11:28:017PM",1024,1035,null,0,null,5,null,0,"","",0,"May 7 2008 3:11:27:923PM")
set identity_insert TimeEntry off
set identity_insert TimeEntry on
if not exists(select * from TimeEntry where TimeEntryID=1394)
Insert into TimeEntry(TimeEntryID,ProjectID,WorkAreaPayItemID,TimeEntryStart,TimeEntryEnd,TimeEntryCreated,TimeEntryDuration,TimeEntryDescription,TimeEntryDate,TimeEntryCreatorID,TimeEntryUserID,OriginatingTimeEntryID,isOT,OTComments,Status,OTWorkAreaPayItemID,PlantID,WorkDesc,Materials,Last_Mod_UserID,Last_Mod_Date)
Values ( 1394,35,70,"May 9 2008 7:00:00:000AM","May 9 2008 3:00:00:000PM","May 7 2008 3:11:28:017PM",4.00,"ncncnbxcn","May 7 2008 3:11:27:923PM",1024,1035,1393,1,null,1,74,0,"","",null,"May 7 2008 3:11:28:017PM")
set identity_insert TimeEntry off
set identity_insert TimeEntry on
if not exists(select * from TimeEntry where TimeEntryID=1395)
Insert into TimeEntry(TimeEntryID,ProjectID,WorkAreaPayItemID,TimeEntryStart,TimeEntryEnd,TimeEntryCreated,TimeEntryDuration,TimeEntryDescription,TimeEntryDate,TimeEntryCreatorID,TimeEntryUserID,OriginatingTimeEntryID,isOT,OTComments,Status,OTWorkAreaPayItemID,PlantID,WorkDesc,Materials,Last_Mod_UserID,Last_Mod_Date)
Values ( 1395,35,70,"Jun 5 2008 12:30:00:000AM","Jun 5 2008 11:30:00:000PM","Jun 5 2008 11:21:31:367AM",23.00,"fefefefefef","Jun 5 2008 1:50:46:043PM",1024,1040,null,0,null,5,null,0,"","",0,"Jun 5 2008 11:21:31:367AM")
set identity_insert TimeEntry off
set identity_insert TimeEntry on
if not exists(select * from TimeEntry where TimeEntryID=1396)
Insert into TimeEntry(TimeEntryID,ProjectID,WorkAreaPayItemID,TimeEntryStart,TimeEntryEnd,TimeEntryCreated,TimeEntryDuration,TimeEntryDescription,TimeEntryDate,TimeEntryCreatorID,TimeEntryUserID,OriginatingTimeEntryID,isOT,OTComments,Status,OTWorkAreaPayItemID,PlantID,WorkDesc,Materials,Last_Mod_UserID,Last_Mod_Date)
Values ( 1396,35,70,"Jun 6 2008 12:00:00:000AM","Jun 6 2008 11:45:00:000PM","Jun 5 2008 11:23:32:580AM",17.00,"efefefefefe","Jun 5 2008 1:50:48:357PM",1024,1040,null,0,null,5,null,0,"","",0,"Jun 5 2008 11:23:32:580AM")
set identity_insert TimeEntry off
set identity_insert TimeEntry on
if not exists(select * from TimeEntry where TimeEntryID=1398)
Insert into TimeEntry(TimeEntryID,ProjectID,WorkAreaPayItemID,TimeEntryStart,TimeEntryEnd,TimeEntryCreated,TimeEntryDuration,TimeEntryDescription,TimeEntryDate,TimeEntryCreatorID,TimeEntryUserID,OriginatingTimeEntryID,isOT,OTComments,Status,OTWorkAreaPayItemID,PlantID,WorkDesc,Materials,Last_Mod_UserID,Last_Mod_Date)
Values ( 1398,35,70,"Jun 6 2008 12:00:00:000AM","Jun 6 2008 3:00:00:000PM","Jun 5 2008 1:50:36:120PM",15.00,"ewyweytwety","Jun 5 2008 1:50:48:370PM",1024,1040,null,1,null,1,74,0,"","",0,"Jun 5 2008 1:50:36:120PM")
set identity_insert TimeEntry off
set identity_insert TimeEntry on
if not exists(select * from TimeEntry where TimeEntryID=1399)
Insert into TimeEntry(TimeEntryID,ProjectID,WorkAreaPayItemID,TimeEntryStart,TimeEntryEnd,TimeEntryCreated,TimeEntryDuration,TimeEntryDescription,TimeEntryDate,TimeEntryCreatorID,TimeEntryUserID,OriginatingTimeEntryID,isOT,OTComments,Status,OTWorkAreaPayItemID,PlantID,WorkDesc,Materials,Last_Mod_UserID,Last_Mod_Date)
Values ( 1399,35,70,"Jun 6 2008 12:00:00:000AM","Jun 6 2008 11:45:00:000PM","Jun 5 2008 1:50:48:357PM",6.75,"efefefefefe","Jun 5 2008 11:23:40:833AM",1024,1040,1396,1,null,1,74,0,"","",null,"Jun 5 2008 1:50:48:357PM")
set identity_insert TimeEntry off
June 5, 2008 at 1:30 pm
Your code seems to work correctly.
I've tested your code (in tempdb) with
DECLARE @ProjectID int
DECLARE @TimeEntryStart datetime
DECLARE @TimeEntryEnd datetime
DECLARE @TimeEntryUserID int
SET @ProjectID=35
SET @TimeEntryStart=convert(datetime,'2008-04-14 02:30:00.000')/*RETURNS 1*/
SET @TimeEntryStart=getdate()/*returns NULL*/
SET @TimeEntryEnd=NULL
SET @TimeEntryUserID=1035
SELECT [tempdb].[dbo].[f_TimeEntryOverlap]
(@ProjectID, @TimeEntryStart, @TimeEntryEnd,@TimeEntryUserID)
June 5, 2008 at 8:08 pm
Your function misses those cases where the requested time range surrounds (or "Spans") an existing one. For example, let say that the only time range in your table for January is {Jan-7 to Jan-9} and lets say the requested time range is {Jan-5 to Jan-15}. These obviously overlap, however your function would not detect it as @TimeEntryStart is NOT between any records TimeEntryStart and TimeEntryEnd values.
The correct way to code this is as follows:
if Exists (Select 1 from TimeEntry where ProjectID = @ProjectID
and TimeENtryUserID = @TimeEntryUserID
and @TimeEntryStart < TimeEntryEnd
and @TimeEntryEnd > TimeEntryStart
)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 5, 2008 at 9:03 pm
I see your point, but I don't think that's right either.
This seems to be working for me and covers all points. Thanks for the highlight.
Declare @Resultbit;
Set @Result = 0;
If Exists (Select 1 from TimeEntry where ProjectID = 35
and TimeENtryUserID = 1040
and (@TimeEntryStart between TimeEntryStart and TimeEntryEnd
or @TimeEntryEnd between TimeEntryStart and TimeEntryEnd))
BEGIN
Set @Result = 1;
END
June 5, 2008 at 10:26 pm
Santiago E. Perez (6/5/2008)
I see your point, but I don't think that's right either.
Sorry, but it is right. I've been using this logic for over 30 years. Feel free to come up with a counter-example if you don't believe me.
This seems to be working for me and covers all points. Thanks for the highlight.
Declare @Resultbit;
Set @Result = 0;
If Exists (Select 1 from TimeEntry where ProjectID = 35
and TimeENtryUserID = 1040
and (@TimeEntryStart between TimeEntryStart and TimeEntryEnd
or @TimeEntryEnd between TimeEntryStart and TimeEntryEnd))
BEGIN
Set @Result = 1;
END
Well this does twice the work of mine, and is also incorrect. Please see the earlier example that I gave and notice that this test still does not pick it up.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply