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
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