RE: SQL question

On Mon, 2005-10-31 at 10:05 -0500, Wilt, Charles wrote:
> Rich,
> 
> Everybody's pretty much told you this isn't going to work due to the use
> of a expression on the columns in the where clause.

Right, the expression makes it table scan.  However, it is working
GREAT.  Don Fisher came up with the solution in a previous e-mail, which
skips the expression:

(DATE_YY > SelectedYear or 
 (DATE_YY = SelectedYear and 
  (DATE_MM > SelectedMonth or
   DATE_MM = SelectedMonth and 
   DATE_DD >= SelectedDay))) and 
(DATE_YY < Selected Year or
 (DATE_YY = SelectedYear and
  (DATE_MM < SelectedMonth or 
   DATE_MM = Selected Month and 
   DATE_DD <= SelectedDay)))

And even though it looks a bit complex, it runs very quickly.

> 
> I can think of two possibilities....
> 
> 1) Perhaps a DDS logical keyed on a new date field that concats the
> current fields.  I'm not sure if this would really help.
It could be fun to try.

> 
> 2) Build upon the idea that you can get it too work by specifying all
> dates in the where clause.  Perhaps a Table UDF that outputs the dates
> in the selected range.  The you can join the table UDF to the original
> data.  Figuring out the best way to code the table UDF will be fun. :-)
I would have done this if necessary.  My concern with this approach is
that one could exhaust even a very large string with just a reasonably
sized date range.

> 
> Also, note that if you limit the select date range to a given month,
> then you only need where year = 2005 and month = 10, you don't need to
> specify all the days.  Can you have any such limitations or do you need
> to allow any begin date to any end date?
That date range was just used to select a small subset from a large file
for testing purposes.  In reality, it could be any date range the user
selects.
> 

> 

Thanks.

Regards,
Rich