January 17, 2006 at 1:30 pm
I need some help excluding dates and time ranges from a result set... We have an application where every time an event happens, a record gets logged to a table. One of the columns on the table is a timestamp. The GUI reporting appication allows some ad-hoc reporting on the table, and one of the filter criteria is a date range, for example, show me all events that happened between the specified start and end dates. We would like to provide the ability for the user to configure excluded dates, which I think is the easy part. However, we also would like to provide the ability for them to exclude an hourly range, by a day of week. For example, do not include any records that are on Mondays, between 12:00am and 8:00am, on Tuesdays, between 10:00am and 2:00pm, etc... Anyone done anything similar or have any ideas?
January 17, 2006 at 1:48 pm
make a udf that returns a 1 or 0 (Y or N)
give it parameters:
check_date
include_or_exclude I/E
from_year_no 0,####
to_year_no 0,####
from_month_no 0,1-12
to_month_no 0,1-12
from_dom_no 0,1-31
to_dom_no 0,1-31
from_dow_no 0,1-7
to_dow_no 0,1-7
from_hour_no 0,1-24
to_hour_no 0,1-24
from_min_no 0,1-60
to_min_no 0,1-60
from_sec_no 0,1-60
to_sec_no 0,1-60
from_ms_no 0,1-999
to_ms_no 0,1-999
Have it evaluate check_date as matching the criteria
(If a parm is 0 then don't check that part of the date)
If you like create a wrapper function that allows parameters like this;
check_date, include_exclude, from_date, to_date
(e.g. getdate(), 'E', 'monday 8:00', 'wednesday 16:30')
that can translate the string date/date portions to the numeric parms of the base evaluate function
then post it here....
assuming someone hasn't already written this and posted it here already.
This won't make use of indexes though, so may be slow. An alternative is a utility proc that will create range entries in a temp table based on your criteria and the max/min dates of the source table, then your queries become a two step process (1. gen a temp table with include or exclude ranges, 2. join to this temp table for filter)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply