Solved

ASP SQL Age Query?

Posted on 2004-04-29
16
290 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now