Filtering by Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Filtering by Dates

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2013-05-28 : 00:23:24

With the example table as follows:

declare @AbsenceLedger table (
ID int,
StartDate date,
EndDate date
)

insert into @AbsenceLedger(
ID,
StartDate,
EndDate
)
values
(1, '04-01-2013', '04-05-2013'),
(2, '04-28-2013', '05-09-2013'),
(3, '06-13-2013', '06-19-2013')

On the front end the user is able to select a start and end date to filter the results.

I am using a simple query like so based on what the user has set as the start and end date:

select * from @AbsenceLedger WHERE StartDate >= @pStartDate AND EndDate <= @pEndDate

Has been working fine so far. However, a client has requested that they would like to see items returned that may fall out of the date filter the way it's working now.

For example, if they wanted to see all items for May and selected '05-01-2013' to '05-31-2013'

The query would be:
select * from @AbsenceLedger WHERE StartDate >= '05-01-2013' AND EndDate <= '05-31-2013'

In this case they would like the 2nd item to be returned, the one from 04-28 to 05-09

How do I modify the query to include that row?

Seems like it should be easy to do, but I am struggling with it.

Thanks.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2013-05-28 : 00:47:59

Are you looking for this?

select * from @AbsenceLedger WHERE EndDate between '05-01-2013' and '05-31-2013'

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 00:49:16

Sorry didnt get that. do you mean regardless of filter you need to get item details in case filter produces no results?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-28 : 05:15:06

maybe add this?

OR (EndDate >= '05-01-2013' AND EndDate <= '05-31-2013')

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-28 : 06:35:51

You might need to impose the @startDate and @endDate individually on both fields...
e.g. StartDate between @pStartDate and @pEndDate OR EndDate between @pStartDate and @pEndDate

select * from @AbsenceLedger WHERE (StartDate between @pStartDate and @pEndDate) OR (EndDate between @pStartDate and @pEndDate)

Cheers
MIK

rypi
Yak Posting Veteran

55 Posts

Posted - 2013-05-28 : 16:52:35

Thanks for the advice everyone, much appreciated.
You've given me some ideas to try.

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 01:03:03

quote:
Originally posted by rypi

Thanks for the advice everyone, much appreciated.
You've given me some ideas to try.

Thanks again.

But we're still not clear of what you were actually looking at!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-29 : 07:40:02

quote:
Originally posted by visakh16

quote:
Originally posted by rypi

Thanks for the advice everyone, much appreciated.
You've given me some ideas to try.

Thanks again.

But we're still not clear of what you were actually looking at!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Perhaps the requester is looking for "Overlapping records by mean of either Start or End dates"; a query to find out records where "either" the start "or" end date is in between the @startDate and @EndDate parameters

Cheers
MIK

rypi
Yak Posting Veteran

55 Posts

Posted - 2013-05-29 : 11:27:39

Sorry for not clarifying a little more.
Basically if the date range entered by the user overlaps in any way with an item in the table it will be returned. Here's a few examples:


user selected range        row ID's returned
04-01-2013 to 04-30-2013       1,2
05-01-2013 to 05-31-2013       2 
05-09-2013 to 05-10-2013       2
06-01-2013 to 06-13-2013       3
04-06-2013 to 04-27-2013       null

I think MIK_2008's suggestion should work,
WHERE (StartDate between @pStartDate and @pEndDate) OR (EndDate between @pStartDate and @pEndDate)

Hopefully that explains things a little better.

Thanks for the responses, much appreciated.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-29 : 13:49:11

Try this:
[CODE]

declare @AbsenceLedger table (
ID int,
StartDate date,
EndDate date
)

insert into @AbsenceLedger(
ID,
StartDate,
EndDate
)
values
(1, '04-01-2013', '04-05-2013'),
(2, '04-28-2013', '05-09-2013'),
(3, '06-13-2013', '06-19-2013')

DECLARE @pStartDate Date = '04-01-2013'
DECLARE @pEndDate Date = '04-30-2013'

select @pStartDate AS StartDate, @pEndDate AS EndDate,
STUFF((SELECT ',' + CAST(ID AS VARCHAR(3)) FROM @AbsenceLedger WHERE (StartDate between @pStartDate and @pEndDate) OR (EndDate between @pStartDate and @pEndDate)
FOR XML PATH('')),1,1,'') AS RowIDsReturned

[/CODE]

rypi
Yak Posting Veteran

55 Posts

Posted - 2013-05-29 : 19:31:30

I implemented the query as I described in my last post:


select * from @AbsenceLedger WHERE (StartDate between @pStartDate and @pEndDate) OR (EndDate between @pStartDate and @pEndDate)

After doing some testing I realized that wasn't going to work. A scenario like @pStartDate = '05-01-2013' and @pEndDate = '05-01-2013'
would not pick up that 2nd item, which I want it to.

I am now testing this query and everything seems to be working as it should. Does anyone see any issues with this?:


SELECT * FROM @AbsenceLedger AL WHERE ((AL.StartDate between @pStartDate AND @pEndDate OR @pStartDate between AL.StartDate AND AL.EndDate) OR 
(AL.EndDate between @pStartDate AND @pEndDate OR @pEndDate between AL.StartDate AND AL.EndDate))

Thanks

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-29 : 19:57:42

Try this:
[CODE]
SELECT @pStartDate AS StartDate, @pEndDate AS EndDate, STUFF((SELECT ',' + CAST(ID AS VARCHAR(3)) FROM @AbsenceLedger
WHERE (StartDate <= @pEndDate AND EndDate >= @pStartDate)
FOR XML PATH('')),1,1,'') AS RowIDsReturned

[/CODE]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-30 : 07:12:43

quote:
Originally posted by rypi
After doing some testing I realized that wasn't going to work. A scenario like @pStartDate = '05-01-2013' and @pEndDate = '05-01-2013'
would not pick up that 2nd item, which I want it to.

Are you use it is not picking the second row????

Declare @pStartDate date,@pEndDate date
set @pStartDate='2013-05-01'
set @pEndDate='2013-05-31'
declare @AbsenceLedger table (ID int,StartDate date,EndDate date)
insert into @AbsenceLedger(ID,StartDate,EndDate)
values (1, '2013-04-01', '2013-04-05'),(2, '2013-04-28', '2013-05-09'),(3, '2013-06-13', '2013-06-19')
select * from @AbsenceLedger WHERE (StartDate between @pStartDate and @pEndDate) OR (EndDate between @pStartDate and @pEndDate)

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources