Query using comparison between two fields as criteria

[Jeff VanBishler]

Hi all,

I have an Access database that is running against a SQL Server, thus my
database is an ADP project. I only mention this because I know there are
some functions available for native Access databases that aren't available
for SQL Server dbs and vice versa. I couldn't find an appropriate SQL Server
group to post this question to, but if someone knows of one that would be
more appropriate please point the way.

Enough of the preliminaries; on to my question!

Let's say that in my above database I have a table with three fields:
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

So now let's say that 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, it's only the latter of the two dates
that we're looking at and that date, 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

 

[Tom Ellison]

Dear Jeff:

I came up with this:

SELECT *
FROM MyTable
WHERE IIf(effective_date > entry_date, effective_date, entry_date)
BETWEEN #1/1/2004# AND #12/31/2004#

You need to replace the name MyTable with the actual name of your table.

Does this help? It works here, giving the results you showed.

Tom Ellison

 

[Jeff VanBishler]

Thanks for the reply and the suggestion Tom. Unfortunately, the IIf function
doesn't exist in the SQL Server world and since I am just using Access as a
front-end to my SQL Server database, I have to limit myself to functions
that are available in SQL Server.

I probably should have posted this question in a SQL Server group, but I
couldn't find one that focused on queries like this one.

-Jeff VanBishler

 

[arthurjr07]

SELECT * FROM MyTABLE WHERE (
CASE
WHEN effective_date>entry_date THEN effective_date
WHEN effective_date<entry_date THEN entry_date
WHEN effective_date=entry_date THEN entry_date
END ) BETWEEN '1/1/2004' AND '12/31/2004'

 

[Jeff VanBishler]

Thank you for the suggestion! Someone in a SQL Server group suggested
something very similar and it worked great. I hope others will benefit from
your wisdom as I have!

-Jeff