If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Not quite as easy as that. The first condition is generated through a software program; the second condition is what I have full control over. So the only code I have access to is the second condition following the 'and' statement.

That's why I'm looking to reference the max of the two previously identified values. Thanks.

Not quite as easy as that. The first condition is generated through a software program; the second condition is what I have full control over. So the only code I have access to is the second condition following the 'and' statement.

That's why I'm looking to reference the max of the two previously identified values. Thanks.

What part of the query can you not change? Can't you still use the basic idea of having a subquery to select the MAX?

Everything except for the last condition is non-editable. I can use a Max sub query in the last condition, but that sub query will reference the entire table. If a record exists that is beyond the 12/31/2005 date then no results are returned.

I've been trying to find a method to run a sub query against only those records that fall within the initial condition, but since sub query refers back to the entire table, and not the previously identified population, it's identifying records that I don't want to reference.

The problem with this query is that it runs on a record by record basis, so if one record has a maximum value of '11/30/2005' it would be returned. If another record had a maximum value of '12/31/2005', it would also be returned.

What I was looking to accomplish is instead of referring back to the table, refer to the previously defined population, and select max value from the first condition (12/31/2005). This way the sub query wouldn't have to identify the max value for each record, but instead identify the max value of dates that were already defined. Hope that clarifies a little.

So you are just passing in a condition? I thought you were writing the query and one of the conditions was static? Still not really sure which part of the query you are writing and which part is static. Could you rewrite the query and explicitly explain which parts you can/cannot change (why the subquery is difficult)?

Depending on how limited you are, you could also try using the query you already have as the subquery .

I've highlighted the undeditable portion of the query below in red. I also provided an example table. The way the initial date conditions work is that the user selects a population from the software frontend. The query is generated through the software.

I then have the ability to restrict the results that are returned through the final condition (in black). Based on the example table, I'd expect the query to return two results (both of the 12/31/2005 rows). Instead it will return 3 results (Var1 = 1,3,4).

The difficult part is that the user selected dates are variable, so if they only selected '11/30/2005' I would want to return each of the 11/30/2005 rows. Right now it would only return those results for Var1 = 1,4. Hope this helps to clarify. Thanks again for all your help.
Select count(*)
From DateTable
where
(
(Date1 = '11/30/2005')
OR
(Date1 = '12/31/2005')
)
AND
Date1 = (Select Max(dt.Date1) from DateTable dt where dt.Date1 = (DateTable.Date1))

The only location I would have the ability to do that is within the sub-query itself. The rest of the query is generated by the application. If I use the (Fetch first row, Top 1, ...) type commands, it works, but only for the particular Var# that it's running for.

So when it ran on Var1 it would return the 11/30/2005 record, which then would return a count value for the Var1. Since I only what to return those records with a 12/31/2005 record, I'll retrun incorrect results. Thanks for the suggestion though.

You are correct. The red part is controlled by the application. This also includes any Group by or Order by statements that apply to the overall query.

The only location I could use a Group by or Order by in within my sub query. Since the sub query runs on each variable, the condition would return true where Var1 = 1. This would match on the 11/30/2005 records. It would fail on the Var1 = 2 since that would return 1/1/2006, but I would like it to pass for that instance.

I need some way to reference the Date1 values without actually knowing what the user selected. This has been a real thorn in my side. Thanks again.