Compare date to list/range of dates (complicated)

[Jaazaniah]

I have a query that compares appointment dates to a range to time
covered by orders. However, I just realized that this will be
problematic when multiple orders come in the future. Here's what I
want to do in pseudo-SQL:

[dtDate] IN
(SELECT [dtAllDates] FROM [DateSource] WHERE [dtAllDates] BETWEEN
{list of dtOrderDate} AND {list of dtOrderExpiry})

It's getting the list straight that's causing my problem. I want the
functions to be useful a decent time into the future without having to
use VBA to create monsterous dynamic SQL strings on the fly (using
iterative WHERE criteria) to get each list. I will if I have to, but
it seems to me, expecially where this code would be found, that it's
an undue processor burden.

IS there a query solution to something like this?

 

[MGFoster]

I have a query that compares appointment dates to a range to time
covered by orders. However, I just realized that this will be
problematic when multiple orders come in the future. Here's what I
want to do in pseudo-SQL:

[dtDate] IN
(SELECT [dtAllDates] FROM [DateSource] WHERE [dtAllDates] BETWEEN
{list of dtOrderDate} AND {list of dtOrderExpiry})

It's getting the list straight that's causing my problem. I want the
functions to be useful a decent time into the future without having to
use VBA to create monsterous dynamic SQL strings on the fly (using
iterative WHERE criteria) to get each list. I will if I have to, but
it seems to me, expecially where this code would be found, that it's
an undue processor burden.

IS there a query solution to something like this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure if you want contiguous dates or sequentially independent dates
in the "list of OrderDate and OrderExpiry."

If you are using contiguous dates you can just use

BETWEEN low_date_value And hi_date_value

Or, if you want 2 comparisons for each date type:

date_column BETWEEN low_order_date And hi_order_date
AND date_column BETWEEN low_expiry_date And hi_expiry_date

If you're using sequentially independent dates you could create a table
of dates. Change the dates according to your needs every time you run
the query. Then JOIN your work table to the dates table:

SELECT T.date_column
FROM table_name As T INNER JOIN dates_table As D
ON T.date_column = D.date_column
WHERE < more criteria >

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSe5UpYechKqOuFEgEQLvvwCggg4WBrfpzbOCUIqhyfawj9UTB3wAoJ2n
sEvZgVw7EPfzMFxmHLObqTlk
=3SLV
-----END PGP SIGNATURE-----

 

[Jaazaniah]

I have a query that compares appointment dates to a range to time
covered by orders. However, I just realized that this will be
problematic when multiple orders come in the future. Here's what I
want to do in pseudo-SQL:

[dtDate] IN
(SELECT [dtAllDates] FROM [DateSource] WHERE [dtAllDates] BETWEEN
{list of dtOrderDate} AND {list of dtOrderExpiry})

It's getting the list straight that's causing my problem. I want the
functions to be useful a decent time into the future without having to
use VBA to create monsterous dynamic SQL strings on the fly (using
iterative WHERE criteria) to get each list. I will if I have to, but
it seems to me, expecially where this code would be found, that it's
an undue processor burden.

IS there a query solution to something like this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure if you want contiguous dates or sequentially independent dates
in the "list of OrderDate and OrderExpiry."

If you are using contiguous dates you can just use

   BETWEEN low_date_value And hi_date_value

Or, if you want 2 comparisons for each date type:

   date_column BETWEEN low_order_date And hi_order_date
   AND date_column BETWEEN low_expiry_date And hi_expiry_date

If you're using sequentially independent dates you could create a table
of dates.  Change the dates according to your needs every time you run
the query.  Then JOIN your work table to the dates table:

SELECT T.date_column
FROM table_name As T INNER JOIN dates_table As D
   ON T.date_column = D.date_column
WHERE < more criteria >

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSe5UpYechKqOuFEgEQLvvwCggg4WBrfpzbOCUIqhyfawj9UTB3wAoJ2n
sEvZgVw7EPfzMFxmHLObqTlk
=3SLV
-----END PGP SIGNATURE------ Hide quoted text -

- Show quoted text -

I am working with non-contiguous dates, so using a work table, or
custom function that returns a recordset (preferable) seems to be my
only course of action. thanks for the input.