[deke]

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 = "i.e, 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 and sorted based on "care_Need_Last_Name, care_Need_Date".
7. From this recordset, I 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 filter in #7 I then will generate a report (not a part of this issue).


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 but if not, please let me know what else is needed.

Thanks.
deke

[Advertisements]

Hey there Deke,

Sorry for the long awaited reply... From your question I understand your having a problem filtering information already stored in the database. Is this safe then to assume you have a working and populated database already and a functioning program that reads/writes to the database? So all your problem is, is filtering the information through.

Here is sudo code that may help you in your mission to solve your problem (or Atleast help me better understand what it is you need). This is sudo code for sorting the the most recent date for the care type of a certain last name:

function filterDates(LastName : String) {
SQLQuery = "SELECT care_Need_Type FROM databaseName WHERE care_Need_Last_Name = " & LastName & ";"

// Store the query for getting the careType into an array of some sort
PatientCareTypeArray() = SQLExecutionEngine();

// Now you need to select the dates for the patient and also store it in an array (I would recommend making it a type date, but then you need to alter your database for dates instead of varchar to date
SQLQuery = "SELECT care_Need_Date FROM databaseName WHERE care_Need_Last_Name = " & LastName & ";"
PatientCareDateArray() = SQLExecutionEngine();


 // Create a variable for storing the data values
careType = null;
latestDate = null;

// Then from here all you have to do is get latest date from the data stored in the array for the specified patient 
for (i <= PatientCareDateArray.ArraySize()) {
	if (PatientCareDateArray(i) > latestDate) 
	latestDate = PatientCareDateArray(i);
	careType = PatientCareTypeArray(i);
}

// From here you can format the string any way you like since you have the data you need, so you can pass it through in what ever manner.
formatedString = careType & LastName & latestDate

return latestDate
}

Keep in mind this is just a basic function that may need to be changed to suite your exact needs. If you would like I will glady write the actual code for your ASP project if you provide me with some of the code your already have, like the functions you use for connecting to database, ect. But this should be simple enough for you to follow and put into ASP. Please just let me know if this is what you were looking for and if there is anything else I can help with?

Peace Out

[Spike]

Hey there Deke,

Sorry for the long awaited reply... From your question I understand your having a problem filtering information already stored in the database. Is this safe then to assume you have a working and populated database already and a functioning program that reads/writes to the database? So all your problem is, is filtering the information through.

Here is sudo code that may help you in your mission to solve your problem (or Atleast help me better understand what it is you need). This is sudo code for sorting the the most recent date for the care type of a certain last name:

function filterDates(LastName : String) {
SQLQuery = "SELECT care_Need_Type FROM databaseName WHERE care_Need_Last_Name = " & LastName & ";"

// Store the query for getting the careType into an array of some sort
PatientCareTypeArray() = SQLExecutionEngine();

// Now you need to select the dates for the patient and also store it in an array (I would recommend making it a type date, but then you need to alter your database for dates instead of varchar to date
SQLQuery = "SELECT care_Need_Date FROM databaseName WHERE care_Need_Last_Name = " & LastName & ";"
PatientCareDateArray() = SQLExecutionEngine();


 // Create a variable for storing the data values
careType = null;
latestDate = null;

// Then from here all you have to do is get latest date from the data stored in the array for the specified patient 
for (i <= PatientCareDateArray.ArraySize()) {
	if (PatientCareDateArray(i) > latestDate) 
	latestDate = PatientCareDateArray(i);
	careType = PatientCareTypeArray(i);
}

// From here you can format the string any way you like since you have the data you need, so you can pass it through in what ever manner.
formatedString = careType & LastName & latestDate

return latestDate
}

Keep in mind this is just a basic function that may need to be changed to suite your exact needs. If you would like I will glady write the actual code for your ASP project if you provide me with some of the code your already have, like the functions you use for connecting to database, ect. But this should be simple enough for you to follow and put into ASP. Please just let me know if this is what you were looking for and if there is anything else I can help with?

Peace Out