Comparing 2 Date Ranges

Question

Comparing 2 Date Ranges

aruns1978

Valued Member

Points: 62
More actions
January 7, 2005 at 12:44 am

#60908

Hi , i have a table with columns "Name","Task_Start_Date" and Task_End_Date","TASK". so, when a task is assigned to an inidividual an entry is made into this table.
I should validate the entries such that no individual is allocated more that 1 task between a given date Range.
for instance,if "A" is assigned a task between 10th Feb'05 to 20th Mar'05. then, "A" should not be alloted with another Task between these two dates.
Thus, There should not be an entry for "A" between 11th jan '2005 to 10th April '2005.
How do i validate this using T-SQL?

Best Regards,
Arun

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

James Lawrence SSC Journeyman Points: 90 More actions · Answer 1

Well the solution I use depends on whether there are NULL values in the tTask Table. I have a business rule that does not allow any NULL dates in the fields [Task_Start_Date] and [Task_End_Date].

you need to check the existance of the [Task_Start_Date] between the requested Date start [D1] and End [D2], and similarly for [Task_End_Date]. You also need to check for the existance of the converse [D1] between [Task_Start_Date] and [Task_End_Date], similarly [D2].

Is use the following T-SQL Query

SELECT COUNT(tTasks.Name) as TasksInRange

FROM tTasks

WHERE (((tTasks.Task_Start_Date) Between [d1] And [d2])) OR (((tTasks.Task_End_Date) Between [d1] And [d2])) OR ((([d1]) Between [DateFrom] And [DateTo])) OR ((([d2]) Between [tTasks.Task_Start_Date] And [Task_End_Date]))

Then via code or other you check if [TasksInRange] > 0, if so then you cannot use that date range! User Tries again.

hope that helps

rgds

James

aruns1978 Valued Member Points: 62 More actions · Answer 2

That was gr8.....

Thank you very much

Best Regards,

Arun S