question row filter (more of sql query question)

G

Guest

I have a question about dataset rowfilter.
I have a list of event table in a database. each event has start-date and
end-date column. I grab these from db and save to dataset. And then I have
to do rowfilter of dataset to filter based on user's input.

Users can select the following as dropdown for filtering.
- last 30 days
- last 7 days
- today
- all pasts

for example for past 30 days filtering I build the following rowfiltering.

string dFrom = today.AddDays(-30);
string dTo = today;
sFilter += " ([event-start-date] <= #" + dFrom.ToShortDateString() + "# AND
[event-end-date] >= #" + dTo.ToShortDateString() + "#) OR " ;
sFilter += " ([event-start-date] <= #" + dFrom.ToShortDateString() + "# AND
[event-end-date] >= #"+ dFrom.ToShortDateString() +"# AND [event-end-date]
<= #" + dTo.ToShortDateString() + "#) OR " ;
sFilter += " ([event-start-date] >= #" + dFrom.ToShortDateString() + "# AND
[event-start-date] <= #"+ dTo.ToShortDateString() + "# AND [event-end-date]
= #" + dTo.ToShortDateString() + "#) OR " ;
sFilter += " ([event-start-date] >= #" + dFrom.ToShortDateString() + "# AND
[event-end-date] <= #" + dTo.ToShortDateString() + "#) " ;

myDataView.RowFilter = sFilter;

But the above didn't return the correct resultset.

since each event has a start/end date and not easy to filter around to get
the correct result set. Please help!
 
S

Scott Allen

I think you have the following 4 cases to include, where x is a day
and e is an event.

xxxxxxxxx
1) eeeee
2) eeeee
3) eeeee
4) eeeeeeeeeeeee


You could include these events with three OR'ed conditions:

1) event_end_date is between FromDate and ToDate

OR

2) event_start_date is between FromDate and ToDate

OR

3) ToDate is between event_start_date and event_end_date

I think your query is too restrictive.

--
Scott
http://www.OdeToCode.com/blogs/scott/

I have a question about dataset rowfilter.
I have a list of event table in a database. each event has start-date and
end-date column. I grab these from db and save to dataset. And then I have
to do rowfilter of dataset to filter based on user's input.

Users can select the following as dropdown for filtering.
- last 30 days
- last 7 days
- today
- all pasts

for example for past 30 days filtering I build the following rowfiltering.

string dFrom = today.AddDays(-30);
string dTo = today;
sFilter += " ([event-start-date] <= #" + dFrom.ToShortDateString() + "# AND
[event-end-date] >= #" + dTo.ToShortDateString() + "#) OR " ;
sFilter += " ([event-start-date] <= #" + dFrom.ToShortDateString() + "# AND
[event-end-date] >= #"+ dFrom.ToShortDateString() +"# AND [event-end-date]
<= #" + dTo.ToShortDateString() + "#) OR " ;
sFilter += " ([event-start-date] >= #" + dFrom.ToShortDateString() + "# AND
[event-start-date] <= #"+ dTo.ToShortDateString() + "# AND [event-end-date]
= #" + dTo.ToShortDateString() + "#) OR " ;
sFilter += " ([event-start-date] >= #" + dFrom.ToShortDateString() + "# AND
[event-end-date] <= #" + dTo.ToShortDateString() + "#) " ;

myDataView.RowFilter = sFilter;

But the above didn't return the correct resultset.

since each event has a start/end date and not easy to filter around to get
the correct result set. Please help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,733
Messages
2,569,440
Members
44,829
Latest member
PIXThurman

Latest Threads

Top