Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Software Development Software Development
Search Search
Search for:
Tech Support Guy > > >

Involved ADO Recordset filtering?


(!)

dekesc's Avatar
dekesc dekesc is offline
Member with 8 posts.
THREAD STARTER
 
Join Date: Aug 2010
Experience: Advanced
26-Jun-2012, 07:26 PM #1
Post Involved ADO Recordset filtering?
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) 3. Table (care_Need_Entries) contains multiple rows of (varchar) columns: care_Need_Last_Name, care_Need_Date, and care_Need_Type (among other cols.). 4. 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&quot. 5. There must exist one "INITIAL" care_Need_Type for each care_Need_Last_Name. 6. There can exist none, one, or multiple "FOLLOWUP" care_Need_Type(s) for each care_Need_Last_Name. 7. An ADO Recordset has been opened and sorted based on "care_Need_Last_Name, care_Need_Date". 8. 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. 9. From the resultant filter in #7 I then will geneate 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. dekesc
ckphilli's Avatar
ckphilli   (Chris) ckphilli is offline
Member with 4,284 posts.
 
Join Date: Apr 2006
Location: Down South
Experience: Intermediate
26-Jun-2012, 09:00 PM #2
Have you re-read your post? It's a jumbled mess and I have no idea what you need help with.

Use code tags to provide what you have so far. Highlight your code and click the hash in the editing toolbar.

Code:
Like so
dekesc's Avatar
dekesc dekesc is offline
Member with 8 posts.
THREAD STARTER
 
Join Date: Aug 2010
Experience: Advanced
27-Jun-2012, 09:41 AM #3
The reason for the scrambled post is b/c none of the browsers I have can't format the pages of this website. I have tried IE, Chrome and FireFox. The Error Console in FF says this: ----------------------------- Timestamp: 6/27/2012 9:37:51 AM Error: The stylesheet http://attach.tsgstatic.com/min/?ver...uicksearch.css was not loaded because its MIME type, "text/html", is not "text/css". Source File: http://forums.techguy.org/45-software-development/ Line: 0 -------------------------------------------
Ent's Avatar
Ent   (Josiah) Ent is offline Ent is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 5,270 posts.
 
Join Date: Apr 2009
Location: United Kingdom
Experience: Intermediate
27-Jun-2012, 10:00 AM #4
Quote:
Originally Posted by dekesc View Post
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)
  3. Table (care_Need_Entries) contains multiple rows of (varchar) columns: care_Need_Last_Name, care_Need_Date, and care_Need_Type (among other cols.).
  4. 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".
  5. There must exist one "INITIAL" care_Need_Type for each care_Need_Last_Name.
  6. There can exist none, one, or multiple "FOLLOWUP" care_Need_Type(s) for each care_Need_Last_Name.
  7. An ADO Recordset has been opened and sorted based on "care_Need_Last_Name, care_Need_Date".
  8. 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.
  9. From the resultant filter in #7 I then will geneate 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. dekesc
That's a bit easier to read now.
I would suggest that you take the browser question to the comments and suggestions subforum if it persists. The site works fine for me in Firefox, IE, Opera, and Google Chrome, but sometimes you do get corrupted scripts and the like. Before you do so, try just clearing your browser cache and cookies before restarting your browser.
ckphilli's Avatar
ckphilli   (Chris) ckphilli is offline
Member with 4,284 posts.
 
Join Date: Apr 2006
Location: Down South
Experience: Intermediate
27-Jun-2012, 10:06 AM #5
Thanks Ent.

dekesc,

What do you have for your current select statement?
ckphilli's Avatar
ckphilli   (Chris) ckphilli is offline
Member with 4,284 posts.
 
Join Date: Apr 2006
Location: Down South
Experience: Intermediate
27-Jun-2012, 10:53 AM #6
This should get you started if you're stumped...

Code:
SELECT
     care_Need_Type,
     care_Need_Last_Name,
     MAX(care_Need_Date) AS "MostRecent"
FROM care_Need_Entries
GROUP BY   care_Need_Type, care_Need_Last_Name
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
ado recordset, asp, ms sql, vbscript

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑