Query using comparison between two fields as criteria

Hi all,
Apologies if this is the incorrect group. I couldn't find a group that
specifically handled queries in SQL Server. I originally posted this in the
Access Queries group, but since my back-end database is actually SQL Server
and I am just using Access as my front-end I need suggestions that are
specific to SQL Server.
Let's say I have a table with three fields in it: effective_date, entry_date
and amount. Is there a way that I could contruct a query such that I could
return all the records from the table where the latter of the effective_date
and entry_date fields falls within some target date range?
So for example, let's say the following data is in my table:
effective_date entry_date amount
1/1/2003 4/1/2004 $200
6/1/2004 9/1/2003 $120
5/1/2004 6/1/2005 $150
7/1/2004 4/1/2004 $170
2/1/2006 8/1/2004 $130
I want all the records where the *latter* of the two date fields falls
between 1/1/2004 and 12/31/2004. What I'd need to see in my result set based
on the data above is:
effective_date entry_date amount
1/1/2003 4/1/2004 $200
6/1/2004 9/1/2003 $120
7/1/2004 4/1/2004 $170
The third data item dropped out because even though the effective_date for
that record falls within our range, the latter of the two dates, the entry
date in this case, falls outside of our interested date range. The fifth
data item similarly falls out because its latest date, the effective date,
also falls outside our range.
How do I write a query to accomplish this?
Thanks for any help!
-Jeff VanBishlerThis is a good candidate for the CASE expression in SQL:
SELECT effective_date, entry_date, amount
FROM [Your Table]
WHERE
CASE
WHEN DATEDIFF(dd, effective_date, entry_date) < 0 THEN effective_date
ELSE entry_date
END
BETWEEN '1/1/2004' AND '12/31/2004'
The DATEDIFF function above is an easy way to find out which date is later
than the other. If effective_date is prior to entry_date, the DATEDIFF
function will return a number greater than one. If entry_date is prior to
effective_date, it will return a number less than one.
"Jeff VanBishler" wrote:

> Hi all,
> Apologies if this is the incorrect group. I couldn't find a group that
> specifically handled queries in SQL Server. I originally posted this in th
e
> Access Queries group, but since my back-end database is actually SQL Serve
r
> and I am just using Access as my front-end I need suggestions that are
> specific to SQL Server.
> Let's say I have a table with three fields in it: effective_date, entry_da
te
> and amount. Is there a way that I could contruct a query such that I could
> return all the records from the table where the latter of the effective_da
te
> and entry_date fields falls within some target date range?
> So for example, let's say the following data is in my table:
> effective_date entry_date amount
> 1/1/2003 4/1/2004 $200
> 6/1/2004 9/1/2003 $120
> 5/1/2004 6/1/2005 $150
> 7/1/2004 4/1/2004 $170
> 2/1/2006 8/1/2004 $130
> I want all the records where the *latter* of the two date fields falls
> between 1/1/2004 and 12/31/2004. What I'd need to see in my result set bas
ed
> on the data above is:
> effective_date entry_date amount
> 1/1/2003 4/1/2004 $200
> 6/1/2004 9/1/2003 $120
> 7/1/2004 4/1/2004 $170
> The third data item dropped out because even though the effective_date for
> that record falls within our range, the latter of the two dates, the entry
> date in this case, falls outside of our interested date range. The fifth
> data item similarly falls out because its latest date, the effective date,
> also falls outside our range.
> How do I write a query to accomplish this?
> Thanks for any help!
>
> -Jeff VanBishler
>
>|||Worked like a dream!
Thanks Mark!
-Jeff VanBishler
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:E5710BB0-9319-43C2-88A8-2E6307B9A5CD@.microsoft.com...
> This is a good candidate for the CASE expression in SQL:
> SELECT effective_date, entry_date, amount
> FROM [Your Table]
> WHERE
> CASE
> WHEN DATEDIFF(dd, effective_date, entry_date) < 0 THEN effective_date
> ELSE entry_date
> END
> BETWEEN '1/1/2004' AND '12/31/2004'
> The DATEDIFF function above is an easy way to find out which date is later
> than the other. If effective_date is prior to entry_date, the DATEDIFF
> function will return a number greater than one. If entry_date is prior to
> effective_date, it will return a number less than one.
> "Jeff VanBishler" wrote:
>