Query help

  • 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');

  • 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


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Sorry, output should be something like select from mytasklist where conflict dates exist. If no conflict dates exist, should be nothing.

  • 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.

  • 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.

  • In other words, user needs to see list of conflicted dates so that he knows which new dates to put in.

  • 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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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