April 3, 2013 at 8:15 pm
I have a table as below. It has list of tasks with its start date and end date. When a user try to enter a new task with start date and end date, it has to check with existing task's start date and end date and report which start date or end date will the new task's start date and end date will conflict. A new task entry can’t start before or after or in between those dates. Basically query should pull all the row(s) that conflict with the given start and end date….If none, output will be empty.
Create Table MyTaskList
TaskName VARCHAR(50),
StartDate DATETIME,
EndDate DATETIME
);
INSERT INTO MyTaskList (TaskName, StartDate,EndDate)
VALUES('Task A','03/05/2012','03/08/2012');
INSERT INTO MyTaskList (TaskName, StartDate,EndDate)
VALUES('Task B','03/09/2012','03/12/2012');
INSERT INTO MyTaskList (TaskName, StartDate,EndDate)
VALUES('Task C','03/13/2012','03/15/2012');
April 4, 2013 at 3:18 am
Difficult to know what you want to do because you haven't included expected output for any data.
What is you expected output with the following: -
DECLARE @NewTask VARCHAR(50), @StartDate DATETIME, @EndDate DATETIME;
SET @NewTask = 'TASK D';
SET @StartDate = '2012-03-04';
SET @EndDate = '2012-03-05';
DECLARE @NewTask VARCHAR(50), @StartDate DATETIME, @EndDate DATETIME;
SET @NewTask = 'TASK D';
SET @StartDate = '2012-03-14';
SET @EndDate = '2012-03-17';
Here's a shot in the dark: -
DECLARE @NewTask VARCHAR(50), @StartDate DATETIME, @EndDate DATETIME;
SET @NewTask = 'TASK D';
SET @StartDate = '2012-03-14';
SET @EndDate = '2012-03-17';
IF EXISTS (SELECT 1 FROM MyTaskList WHERE @StartDate >= StartDate AND @StartDate <= EndDate)
BEGIN
PRINT 'StartDate No Good';
END
ELSE IF EXISTS (SELECT 1 FROM MyTaskList WHERE @EndDate >= StartDate AND @EndDate <= EndDate)
BEGIN
PRINT 'EndDate No Good';
END
ELSE BEGIN
INSERT INTO MyTaskList (TaskName, StartDate,EndDate)
SELECT @NewTask, @StartDate, @EndDate;
END
April 4, 2013 at 4:31 am
Sorry, output should be something like select from mytasklist where conflict dates exist. If no conflict dates exist, should be nothing.
April 4, 2013 at 5:08 am
SQL_Surfer (4/4/2013)
Sorry, output should be something like select from mytasklist where conflict dates exist. If no conflict dates exist, should be nothing.
This doesn't help me. Using the limited sample data provided show us examples of both.
April 4, 2013 at 6:09 am
Let me see if this helps. Select list of conflicted dates from the table with the new startdate or enddate or both. I am thinking of putting each condition in union but I could be thinking wrong.
April 4, 2013 at 6:12 am
In other words, user needs to see list of conflicted dates so that he knows which new dates to put in.
April 4, 2013 at 6:46 am
I note that you still haven't scripted out your expected outcome based on sample data. Unfortunately, this means I'm unable to provide any more assistance.
Good luck getting everything working.
April 4, 2013 at 6:51 am
SQL_Surfer (4/4/2013)
In other words, user needs to see list of conflicted dates so that he knows which new dates to put in.
Words, all I see are words. We need to see True examples, using the bare sample data provided if (some data) is passed into (some code, not expecting this part) the expected result based on the input and the sample data provide is (what).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply