Complex filtering of an ADO Recordset

[dekesc]

I am not able to do the involved filtering of an ado recordset, as described below. I have tried several approaches but no success. Here's the scenario...

1. Using vbscript and asp
2. Databse = MS SQL (2008)
2. Table (care_Need_Entries) contains multiple rows of (varchar) columns: care_Need_Last_Name, care_Need_Date, and care_Need_Type (among other cols.).
3. Each care_Need_Last_Name can have one or more occurrences with care_Need_Type = "INITIAL" and/or "FOLLOWUP" along with having a unique varchar date (care_Need_Date = "e.g., 10/14/2012").
4. There must exist one "INITIAL" care_Need_Type for each care_Need_Last_Name.
5. There can exist none, one, or multiple "FOLLOWUP" care_Need_Type(s) for each care_Need_Last_Name.
6. An ADO Recordset has been opened, SELECTED, and sorted based on "care_Need_Last_Name, care_Need_Date".
7. From this recordset, I now need to filter (keep) only 'the most recent date' row for each care_Need_Last_Name, whether it be just the "INITIAL" or the last "FOLLOWUP" care_Need_Type.
8. From the resultant filtered recordset (or a copy?) in #7 I will thne be generating a report (not a part of this issue) of the most recent rows/care_Need_Last_Name.


Example db data...

care_Need_Type care_Need_Last_Name care_Need_Date
INITIAL Jones 5/12/2012
INITIAL Bell 6/14/2012
FOLLOWUP Bell 6/15/2012
INITIAL SPARKS 6/16/2012
FOLLOWUP Bell 6/16/2012
FOLLOWUP Jones 5/17/2012
INITIAL Reese 6/20/2012

The result needed is...

FOLLOWUP Jones 5/17/2012
FOLLOWUP Bell 6/16/2012
INITIAL SPARKS 6/16/2012
INITIAL Reese 6/20/2012


I hope I have explained things well enough but if not, please let me know what else is needed.

Thanks.
dekesc

 

[emozley]

I had a similar problem a while back and found an example query that I was able to adapt to my own needs:

select Name, ApptDate from AppointmentsKept group by Name having AppDate = max(AppDate)

This website here has the full article and explains a bit more background and also some other ways of doing it:

http://retrowebdev.blogspot.co.uk/2006/09/sql-select-distinct-with-most-recent.html

I hope I have understood your problem correctly - in a nutshell it sounds like you want to select the most recent distinct records.

Ed

 

[dekesc]

I changed the SELECT to my parameters and set it up for ADODB and got the following erro masg:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Column 'CareMinistry_test_smallgroups.entry_Date' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

This is the select= rs.Source = "select care_Need_Last_Name, entry_Date from CareMinistry_test_smallgroups group by care_Need_Last_Name having entry_Date = max(entry_Date)"

Any suggestions?

 

[wbodger]

Since you are using SQL 2008 it sounds to me like perhaps you should alter your query if that is possible? You could try using row_number() partitioning and ordering by the appropriate columns. If you do this in a CTE, then you could just grab whichever end of the rs that you need.

wb

 

[dekesc]

Actually, the app requirements have changed. Thus, I am using a different--and workling--SELECT.

Not sure how to mark this issue as resolved. Any suggestion?

 

[wbodger]

I have not seen any functionality to mark things as done.resolved, so I think you just did it!

wb