SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query help


Query help

Author
Message
SQL_Surfer
SQL_Surfer
Say Hey Kid
Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)

Group: General Forum Members
Points: 663 Visits: 1086
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');
Cadavre
Cadavre
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3956 Visits: 8472
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

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
SQL_Surfer
SQL_Surfer
Say Hey Kid
Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)

Group: General Forum Members
Points: 663 Visits: 1086
Sorry, output should be something like select from mytasklist where conflict dates exist. If no conflict dates exist, should be nothing.
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40324 Visits: 38567
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
SQL_Surfer
SQL_Surfer
Say Hey Kid
Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)

Group: General Forum Members
Points: 663 Visits: 1086
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.
SQL_Surfer
SQL_Surfer
Say Hey Kid
Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)Say Hey Kid (663 reputation)

Group: General Forum Members
Points: 663 Visits: 1086
In other words, user needs to see list of conflicted dates so that he knows which new dates to put in.
Cadavre
Cadavre
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3956 Visits: 8472
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

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40324 Visits: 38567
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).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search