February 10, 2009 at 10:07 pm
I want to write the select Query which will select all the records from the table which falls or exceeds the provided date range
My table has columns
ID ProjectId TaskName StartDate EndDate
1 1 Task1 1/20/2008 10/10/2008
2 1 task2 2/26/2008 3/10/2008
3 1 task3 5/25/2008 7/6/2008
4 1 task4 4/30/2008 12/23/2008
5 1 task5 12/25/2008 1/1/2009
6 1 task6 10/12/2008 10/25/2008
7 1 task7 8/9/2008 9/9/2008
8 1 task8 3/22/2008 4/4/2008
9 1 task9 5/2/2008 6/6/2008
10 1 task10 8/12/2008 11/1/2008
I have to select the records for the particular date range
I am taking input parameters as startDateInput and EndDateInput
Example
If startDateInput = 5/5/2008 and EndDateInput = 10/9/2008
Then Output datatable should look like
ID ProjectId TaskName StartDate EndDate
1 1 Task1 1/20/2008 10/10/2008
3 1 task3 5/25/2008 7/6/2008
4 1 task4 4/30/2008 12/23/2008
7 1 task7 8/9/2008 9/9/2008
9 1 task9 5/2/2008 6/6/2008
10 1 task10 8/12/2008 11/1/2008
How to write query for this
How to write query for this
Plz help if you know the answer.
Its Urgent.......
February 11, 2009 at 1:12 am
Hello,
you will get much better response if you will post your questions with table definitions (CREATE TABLE ....) and sample data (INSERT INTO ...).
If I understand correctly, what you need is to find all tasks, that were active for at least one day during the given time (i.e. between startDateInput and EndDateInput - let's call that "Period"). That is, you want to include:
- tasks that started before Period and ended during Period
- tasks that started before Period and ended after Period
- tasks that started during Period and ended during Period
- tasks that started during Period and ended after Period
I'm not sure what you want to do with tasks that have no Enddate - should these be included or ignored?
Generally, if that really is what you need, simply select all rows
possibly with some NULL treatment if you want to include tasks without EndDate.
EDIT : Forum engine is "eating" my code ... can't post it. No idea what's happening 🙁
February 11, 2009 at 1:29 am
OK, I will describe the solution, because the code loses great part when I try to post it, probably everything between "less than" and "greater than". Strange, never happened to me before.
- tasks that started before Period and ended during Period
- tasks that started before Period and ended after Period
- tasks that started during Period and ended during Period
- tasks that started during Period and ended after Period
Together that makes Tasks that started before the end of Period, and ended after the start of Period. That covers all possibilities.
To write a condition that will cover it is simple. Just take all rows where start date is less than (or equal to) InputEndDate, and at the same time end date is greater than (or equal to) InputStartDate.
February 11, 2009 at 1:30 am
Now at one glance I feel that if all you require is the output based on your two paramenters then there is a function which you could make use of.
That would BETWEEN 'parameter1' AND 'parameter2'
so ur code should be something like this
select * from dbo.Table
where date BETWEEN startdate AND enddate
so think that would do for your output...
February 11, 2009 at 2:38 am
Linson.Daniel (2/11/2009)
Now at one glance I feel that if all you require is the output based on your two paramenters then there is a function which you could make use of.That would BETWEEN 'parameter1' AND 'parameter2'
so ur code should be something like this
select * from dbo.Table
where date BETWEEN startdate AND enddate
so think that would do for your output...
Your solution with BETWEEN would work when looking for one particular date. The question as I understand it is to find whether two time periods overlap or not. You don't have one date, but StartDate and EndDate.
February 11, 2009 at 1:52 pm
Where StartDate Between startDateInput and EndDateInput
OR EndDate Between startDateInput and EndDateInput
OR (StartDate "Less than" startDateInput and StartDate "greater than" EndDateInput)
February 11, 2009 at 2:22 pm
If OP wants to select records where the date range in the table row overlaps the date range defined in the input parameters, then Vladan is right, and rtomkins' extra filter criteria are unecessary. The WHERE clause would be
WHERE (StartDate <= @EndDateInput) AND (EndDate >= @StartDateInput)
February 11, 2009 at 3:18 pm
andrewd.smith (2/11/2009)
If OP wants to select records where the date range in the table row overlaps the date range defined in the input parameters, then Vladan is right, and rtomkins' extra filter criteria are unecessary. The WHERE clause would be
WHERE (StartDate <= @EndDateInput) AND (EndDate >= @StartDateInput)
Unless EndDate can be NULL - in which case you need to also check for that:
WHERE StartDate <= @EndDateInput
AND (EndDate >= @StartDateInput OR EndDate IS NULL)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply