Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ASP SQL Age Query?

Posted on 2004-04-29
16
294 Views
Last Modified: 2008-02-26
With the help of ZeeSparrow I've got a search script that can return records based on a number of different search paramteres.

The EE question is here:
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20972803.html#10954241



One of the search parameters is giving me a bit of a headache, an AGE field search.

Basically the database stores a DOB and the search needs to classify records whethe they are in an age group.   The search form submits a numeric identifier which references a age band, suchas:

1 = Under 18
2 = 18 - 25
3 = 26 - 30
4 = 31 - 35
5 = 36 - 40
6 = 41 - 45
7 = 46 - 50
8 = 51 - 55
9 = 56 - 60
10 = Over 60

The database table already has two additional fields that allow easy searches of these groups, the upper and lower range per group.

SearchCriteria_1
SearchCriteria_2

So for group 4, 31-35  these two fields would have 31 and 35, both as integers.


Ok, so the search form field submits a number between 1 and 10 that repesents one of the groups above, OR the form field box is left default and "AnyContent" is submitted.

The questions is how do I write a WHERE clause, into my existing code, that looks at the DOB at the SQL line and classifies into one of the above groups.


The SQL search line I have so far is:

<code>
      SQL_Search_Advanced = "SELECT Business.ID,Business.BusinessName,Business.ICF,Business.CNA,Business.Title_Code,Business.Forename,Business.Surname,Business.Telephone, Membership.BusinessID,Membership.MembershipStatus,Membership.MembershipNumber,Membership.ExpiryDate,Membership.AppointedBSO FROM Business INNER JOIN Membership ON Business.ID = Membership.BusinessID WHERE "

      If (strSearch_Advanced_MembershipStatus <> "AnyContent") Then
           strWhere = strWhere & "Membership.MembershipStatus = '"&strSearch_Advanced_MembershipStatus&"' AND "
      End If

      If (strSearch_Advanced_BSO <> "AnyContent") Then
           strWhere = strWhere & "Membership.AppointedBSO = '" & strSearch_Advanced_BSO &"' AND "
      End If

      If (strSearch_Advanced_Borough <> "AnyContent") Then
           strWhere = strWhere & "Business.Address_Borough = " & strSearch_Advanced_Borough &" AND "
      End If

      If (strSearch_Advanced_Region <> "AnyContent") Then
           strWhere = strWhere & "Business.Address_Region = " & strSearch_Advanced_Region &" AND "
      End If

      If (strSearch_Advanced_Gender <> "AnyContent") Then
           strWhere = strWhere & "Business.Gender = '" & strSearch_Advanced_Gender &"' AND "
      End If

      If (strSearch_Advanced_EthnicOrigin <> "AnyContent") Then
           strWhere = strWhere & "Business.EthnicOrigin_Code = " & strSearch_Advanced_EthnicOrigin &" AND "
      End If

      If (strSearch_Advanced_EmployeeNumber <> "AnyContent") Then
           strWhere = strWhere & "Business.EmployeeNumber = " & strSearch_Advanced_EmployeeNumber &" AND "
      End If      

      If (strSearch_Advanced_DirectoryClassification <> "AnyContent") Then
           strWhere = strWhere & "Business.DirectoryClassification = " & strSearch_Advanced_DirectoryClassification &" AND "
      End If      


      ' removes the trailing " AND "
      If RIGHT( strWhere, 4 ) = "AND " THEN
            strWhere = LEFT( strWhere, LEN( strWhere ) - 4 )  
      End If

      IF LEN(strWhere) = 0 THEN
            strWhere = " 1=1 "
      END IF      
      
      SQL_Search_Advanced = SQL_Search_Advanced & strWhere
</code>

Any thoughts :)
Caspar
0
Comment
Question by:CasparUK
  • 8
  • 4
  • 2
  • +1
16 Comments
 
LVL 1

Author Comment

by:CasparUK
ID: 10956748
This is what I have used previosuly when the age was determined once the recordset was open, basically once all of the other criteria had been satisfied it would always pull ALL content based on 1=1 for the DOB, then when writing each record it would then do a DOB comparison:


     If (strSearch_Advanced_Age <> "AnyContent") Then
            Dim theAge
            theAge = getAge(rs_Search_Advanced_DatabaseTraversal_Age("DOB"),Date())
            
            Dim SQL_Search_Advanced_DatabaseTraversal_Age_Criteria
            SQL_Search_Advanced_DatabaseTraversal_Age_Criteria = "SELECT SearchCriteria_1,SearchCriteria_2 FROM SearchAdvanced_Age WHERE ID=" & strSearch_Advanced_Age

            Dim rsSearch_Advanced_DatabaseTraversal_Age_Criteria
            Set rsSearch_Advanced_DatabaseTraversal_Age_Criteria = Server.CreateObject("ADODB.Recordset")      

            rsSearch_Advanced_DatabaseTraversal_Age_Criteria.Open SQL_Search_Advanced_DatabaseTraversal_Age_Criteria, Connection_02,1,3

            If NOT rsSearch_Advanced_DatabaseTraversal_Age_Criteria.EOF Then            
                  If ( (theAge => rsSearch_Advanced_DatabaseTraversal_Age_Criteria("SearchCriteria_1")) AND (theAge <= rsSearch_Advanced_DatabaseTraversal_Age_Criteria("SearchCriteria_2")) )Then
                        AddThisRecord_SearchFor_Success_Age = True      
                  End If
            End If
            rsSearch_Advanced_DatabaseTraversal_Age_Criteria.Close


     End If    


hope it helps? :)
0
 
LVL 3

Expert Comment

by:rajuare
ID: 10957495
U can use if condition in ur SQL statement .....instead controlling thru CODE. SQL-statmenet is very fast.
0
 
LVL 1

Author Comment

by:CasparUK
ID: 10957532
but how! :)

Any ideas?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 3

Expert Comment

by:rajuare
ID: 10958165
apply ur condition like below ..................

SELECT   Category =
      CASE type
         WHEN 'popular_comp' THEN 'Popular Computing'
         WHEN 'mod_cook' THEN 'Modern Cooking'
         WHEN 'business' THEN 'Business'
         WHEN 'psychology' THEN 'Psychology'
         WHEN 'trad_cook' THEN 'Traditional Cooking'
         ELSE 'Not yet categorized'
      END,
   CAST(title AS varchar(25)) AS 'Shortened Title',
   price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
0
 
LVL 1

Author Comment

by:CasparUK
ID: 10958424
sorry, but I fail to see how that code helps my problem, condsidering the code I already have? :)
0
 
LVL 6

Expert Comment

by:Mike_Metro
ID: 10959900
This should do the trick:  

  SQL_Search_Advanced = "SELECT Business.ID,Business.BusinessName,Business.ICF,Business.CNA,Business.Title_Code,Business.Forename,Business.Surname,Business.Telephone, Membership.BusinessID,Membership.MembershipStatus,Membership.MembershipNumber,Membership.ExpiryDate,Membership.AppointedBSO FROM Business INNER JOIN Membership ON Business.ID = Membership.BusinessID "

     If (strSearch_Advanced_Age <> "AnyContent") Then
         SQL_Search_Advanced = "INNER JOIN SearchAdvanced_Age ON DateDiff(YY, Membership.DOB, getdate()) Between SearchAdvanced_Age SearchCriteria_1  and SearchAdvanced_Age SearchCriteria_2"
     End If
 
     SQL_Search_Advanced = SQL_Search_Advanced & "WHERE "

     If (strSearch_Advanced_MembershipStatus <> "AnyContent") Then
          strWhere = strWhere & "Membership.MembershipStatus = '"&strSearch_Advanced_MembershipStatus&"' AND "
     End If

     If (strSearch_Advanced_BSO <> "AnyContent") Then
          strWhere = strWhere & "Membership.AppointedBSO = '" & strSearch_Advanced_BSO &"' AND "
     End If


etc.....

SELECT SearchCriteria_1,SearchCriteria_2 FROM SearchAdvanced_Age WHERE ID=" & strSearch_Advanced_Age
0
 
LVL 6

Expert Comment

by:Mike_Metro
ID: 10959911
Sorry the last line select searcCriteria etc...is not needed...I forgot to remove it.
0
 
LVL 6

Expert Comment

by:Mike_Metro
ID: 10959973
Small update.  I left out the where for the age criteria.

  SQL_Search_Advanced = "SELECT Business.ID,Business.BusinessName,Business.ICF,Business.CNA,Business.Title_Code,Business.Forename,Business.Surname,Business.Telephone, Membership.BusinessID,Membership.MembershipStatus,Membership.MembershipNumber,Membership.ExpiryDate,Membership.AppointedBSO FROM Business INNER JOIN Membership ON Business.ID = Membership.BusinessID "

     If (strSearch_Advanced_Age <> "AnyContent") Then
         SQL_Search_Advanced = "INNER JOIN SearchAdvanced_Age ON DateDiff(YY, Membership.DOB, getdate()) Between SearchAdvanced_Age SearchCriteria_1  and SearchAdvanced_Age SearchCriteria_2 WHERE
         strWhere = strWhere & "  SearchAdvanced_Age.ID=" & strSearch_Advanced_Age & " AND "
    End if

     SQL_Search_Advanced = SQL_Search_Advanced & "WHERE "
 
     
     If (strSearch_Advanced_MembershipStatus <> "AnyContent") Then
          strWhere = strWhere & "Membership.MembershipStatus = '"&strSearch_Advanced_MembershipStatus&"' AND "
     End If

     If (strSearch_Advanced_BSO <> "AnyContent") Then
          strWhere = strWhere & "Membership.AppointedBSO = '" & strSearch_Advanced_BSO &"' AND "
     End If

etc.....
0
 
LVL 1

Author Comment

by:CasparUK
ID: 10960886
Thanks Mike, but not quite there....  the problem is that the SearchAdvanced_Age is in a different database, so how could I open the database and use the table within there after searching the correct record within the age table, cause the that table contains:

ID, PopulateMe,SearchCriteria_1,SearchCriteria_2

ID is Pkey
Populate me contains:

1 = Under 18
2 = 18 - 25
3 = 26 - 30
4 = 31 - 35
5 = 36 - 40
6 = 41 - 45
7 = 46 - 50
8 = 51 - 55
9 = 56 - 60
10 = Over 60

The upper and lower range per group:

SearchCriteria_1
SearchCriteria_2

So for group 4, 31-35  these two fields would have 31 and 35, both as integers.




Help!  :)

     If (strSearch_Advanced_Age <> "AnyContent") Then

      
           Dim SQL_Search_Advanced_DatabaseTraversal_Age_Criteria
          SQL_Search_Advanced_DatabaseTraversal_Age_Criteria = "SELECT SearchCriteria_1,SearchCriteria_2 FROM SearchAdvanced_Age WHERE ID=" & strSearch_Advanced_Age

          Dim rsSearch_Advanced_DatabaseTraversal_Age_Criteria
          Set rsSearch_Advanced_DatabaseTraversal_Age_Criteria = Server.CreateObject("ADODB.Recordset")    

          rsSearch_Advanced_DatabaseTraversal_Age_Criteria.Open SQL_Search_Advanced_DatabaseTraversal_Age_Criteria, Connection_02,1,3

          If NOT rsSearch_Advanced_DatabaseTraversal_Age_Criteria.EOF Then          
               If ( (theAge => rsSearch_Advanced_DatabaseTraversal_Age_Criteria("SearchCriteria_1")) AND (theAge <= rsSearch_Advanced_DatabaseTraversal_Age_Criteria("SearchCriteria_2")) )Then
                    AddThisRecord_SearchFor_Success_Age = True    
               End If
          End If




         SQL_Search_Advanced = "INNER JOIN SearchAdvanced_Age ON DateDiff(YY, Business.DOB, getdate()) Between SearchAdvanced_Age SearchCriteria_1 AND SearchAdvanced_Age SearchCriteria_2"
     End If
0
 
LVL 2

Accepted Solution

by:
ZeeSparrow earned 241 total points
ID: 10964216
Try something like this

' add this section before the SELECT statement
' this should get you a beginning age (age1) and ending age (age2) -- if needed

     If (strSearch_Advanced_Age <> "AnyContent") Then
          Dim age1, age2
         
          Dim SQL_Search_Advanced_DatabaseTraversal_Age_Criteria
          SQL_Search_Advanced_DatabaseTraversal_Age_Criteria = "SELECT SearchCriteria_1,SearchCriteria_2 FROM SearchAdvanced_Age WHERE ID=" & strSearch_Advanced_Age


          Dim rsSearch_Advanced_DatabaseTraversal_Age_Criteria
          Set rsSearch_Advanced_DatabaseTraversal_Age_Criteria = Server.CreateObject("ADODB.Recordset")    

          rsSearch_Advanced_DatabaseTraversal_Age_Criteria.Open SQL_Search_Advanced_DatabaseTraversal_Age_Criteria, Connection_02,1,3

          If NOT rsSearch_Advanced_DatabaseTraversal_Age_Criteria.EOF Then  
            age1 = rsSearch_Advanced_DatabaseTraversal_Age_Criteria("SearchCriteria_1")
            age2 = rsSearch_Advanced_DatabaseTraversal_Age_Criteria("SearchCriteria_2")
        END IF

          rsSearch_Advanced_DatabaseTraversal_Age_Criteria.Close


     End If    

-- here is the same SQL SELECT statement
SQL_Search_Advanced = "SELECT Business.ID,Business.BusinessName,Business.ICF,Business.CNA,Business.Title_Code,Business.Forename,Business.Surname,Business.Telephone, Membership.BusinessID,Membership.MembershipStatus,Membership.MembershipNumber,Membership.ExpiryDate,Membership.AppointedBSO FROM Business INNER JOIN Membership ON Business.ID = Membership.BusinessID WHERE "

-- add this statement
     If (strSearch_Advanced_Age <> "AnyContent") Then
      strWhere = strWhere & "( DateDiff(m, Membership.DOB, getdate()) / 12 ) Between " & age1 & " and " & age2
     End If
 

-- --------------------------------------

Something like that.
0
 
LVL 6

Expert Comment

by:Mike_Metro
ID: 10969665
SQL_Search_Advanced = "SELECT Business.ID,Business.BusinessName,Business.ICF,Business.CNA,Business.Title_Code,Business.Forename,Business.Surname,Business.Telephone, Membership.BusinessID,Membership.MembershipStatus,Membership.MembershipNumber,Membership.ExpiryDate,Membership.AppointedBSO FROM Business INNER JOIN Membership ON Business.ID = Membership.BusinessID WHERE "


     If (strSearch_Advanced_Age <> "AnyContent") Then
          Dim rsSearch_Advanced_DatabaseTraversal_Age_Criteria, dteStart, dteEnd

          SQL_Search_Advanced_DatabaseTraversal_Age_Criteria = "SELECT SearchCriteria_1,SearchCriteria_2 FROM SearchAdvanced_Age WHERE ID=" & strSearch_Advanced_Age
           Set rsSearch_Advanced_DatabaseTraversal_Age_Criteria = Server.CreateObject("ADODB.Recordset")    
           rsSearch_Advanced_DatabaseTraversal_Age_Criteria.Open SQL_Search_Advanced_DatabaseTraversal_Age_Criteria, Connection_02,1,3
           dteStart = rsSearch_Advanced_DatabaseTraversal_Age_Criteria("SearchCriteria_1")
           dteEnd =  rsSearch_Advanced_DatabaseTraversal_Age_Criteria("SearchCriteria_2")
           rsSearch_Advanced_DatabaseTraversal_Age_Criteria.Close
           Set rsSearch_Advanced_DatabaseTraversal_Age_Criteria = Nothing
           strWhere = strWhere & "DateDiff(YY, Membership.DOB, getdate()) Between '" & dteStart & "' AND '" dteEnd & "' AND "
     End If
 
     If (strSearch_Advanced_MembershipStatus <> "AnyContent") Then
          strWhere = strWhere & "Membership.MembershipStatus = '"&strSearch_Advanced_MembershipStatus&"' AND "
     End If

     If (strSearch_Advanced_BSO <> "AnyContent") Then
          strWhere = strWhere & "Membership.AppointedBSO = '" & strSearch_Advanced_BSO &"' AND "
     End If

     If (strSearch_Advanced_Borough <> "AnyContent") Then
          strWhere = strWhere & "Business.Address_Borough = " & strSearch_Advanced_Borough &" AND "
     End If

etc.....
0
 
LVL 1

Author Comment

by:CasparUK
ID: 11005908
I'm getting the below error when using either of your code:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'getdate' in expression.


I know GetDate() is an SQL function, so am confused why I'm getting an error :(


Any help?
0
 
LVL 1

Author Comment

by:CasparUK
ID: 11006230
ok, since I'm using MS Access looks like I can't use SQL Server Functions!!! :p :)

So I've got this line instead of the above:

strWhere = strWhere & "DateDiff(YY, Membership.DOB, "&Date()&" ) Between " & dteStart & " AND " & dteEnd & " "


but now get the following error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.



? :)
0
 
LVL 1

Author Comment

by:CasparUK
ID: 11006252
p.s. what happens in the code is Membership.DOB is empty, i.e. no date?  :)
0
 
LVL 2

Expert Comment

by:ZeeSparrow
ID: 11008245
Access!  Aha... that's good to know.

You will need to change that line in order to determine accurate ages (number of months between DOB and now, divided by 12, gets you an accurate age - otherwise you may report someone as a year older than they are before their birthday this year).

As for how to handle an empty DOB field, you can add an IIF statement .... something like

strWhere = strWhere & " IIF( Membership.DOB="""" , 0, ( DateDiff(m, Membership.DOB, "&Date()&" ) / 12 ) ) Between " & dteStart & " and " & dteEnd & " "

Might need to modify this for syntax.... and I'm not sure if you want them included when they have a blank Membership.DOB or not, but this should get you headed in the right direction.
0
 
LVL 1

Author Comment

by:CasparUK
ID: 11014061
Thanks guys, we've done it!!! :D


Final code:

      If (strSearch_Advanced_Age <> "AnyContent") Then
            Dim rsSearch_Advanced_DatabaseTraversal_Age_Criteria, dteStart, dteEnd

            SQL_Search_Advanced_DatabaseTraversal_Age_Criteria = "SELECT SearchCriteria_1,SearchCriteria_2 FROM SearchAdvanced_Age WHERE ID=" & strSearch_Advanced_Age
            Set rsSearch_Advanced_DatabaseTraversal_Age_Criteria = Server.CreateObject("ADODB.Recordset")    
            rsSearch_Advanced_DatabaseTraversal_Age_Criteria.Open SQL_Search_Advanced_DatabaseTraversal_Age_Criteria, Connection_02,1,3

            If NOT rsSearch_Advanced_DatabaseTraversal_Age_Criteria.EOF Then  
                  dteStart = rsSearch_Advanced_DatabaseTraversal_Age_Criteria("SearchCriteria_1")
                  dteEnd = rsSearch_Advanced_DatabaseTraversal_Age_Criteria("SearchCriteria_2")
            End If

            rsSearch_Advanced_DatabaseTraversal_Age_Criteria.Close
            Set rsSearch_Advanced_DatabaseTraversal_Age_Criteria = Nothing

            strWhere = strWhere & " ( DateDiff('m',DOB,'" & Date & "') / 12)  BETWEEN " & dteStart & " AND " & dteEnd
      End If



Looking at the code, it seems to have grown only out of ZeeSparrow code... :)   With some of my modifications ;)


Thanks,
Cas
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question