EasyCFM.COM ColdFusion Forums / Coding Help! / Select Date, area and if not on holiday

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Select Date, area and if not on holiday

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

cabry
08-16-2006 @ 9:06 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 94
Joined: Dec 2005

Hi I am creating a lead generation website.

I need to select brokers from a database based on the following things

• Time- It will select the broker who received a lead the longest time ago
• Area- The brokers have areas that they want to receive leads from. They are displayed in the database like RM,IG,CO,EC
• Not on holiday- There is a check box which is in the Database. If it is checked it means that they are on holiday and we do not want them to receive leads.

The following code is what I used to select the broker based on the longest time. I need help to search for the other two areas

<cfquery name="test" datasource="shop4leads" dbtype="ODBC">

SELECT TOP 1 brokerId,contactname
FROM broker
WHERE dateandtimelastvisited = (SELECT MIN(dateandtimelastvisited)
FROM broker)
AND dateandtimelastvisited IS NOT NULL

</cfquery>


Please help i need to complete this by thursday eve


Thanx

cabry
09-20-2006 @ 5:31 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 94
Joined: Dec 2005

Hi i know have the following code but it returns no results

My code is

SELECT TOP 1 brokerid,contactname
FROM broker
WHERE  dateandtimelastvisited = (SELECT MIN(dateandtimelastvisited)FROM broker)
AND postcodeareas like '%RM%'


I have been told that i need to add the postcode part to my sub query but i dont know how to do this.

I also need to add another query so that they only select a broker who's holiday is equals to no

Please help me

SirRawlins
09-20-2006 @ 5:47 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 951
Joined: Mar 2006

It shouldn't be too hard to build what you're looking for.

But its very tough to picture things without knowing which fields you have in your database, and how you want the data to be displayed.

Can you let us know the column names from the DB, the DB type and maybe some sample data thats in the database, and what you want it to output, we can then help place that query together.

Thanks,

Rob

cabry
09-21-2006 @ 6:46 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 94
Joined: Dec 2005

Hi i am using an access database.  There are two tables Broker and Lead

broker Table
Brokerid (Primary Key)
Companyname
Contactname
Contactemail
Contactaddress1
Contactaddress2
Town
county
Postcode
Telephone
Password
Memorable question
Memorable answer
postcodeareas
Amountperday
Amountperweek
Dateandtimelastlead
holidayperiod

Lead Table
This just inserts the lead into the database so that we have a record of all leads


Sample Data

1                                        448001
    HBK Investment

    Carly Bryan


      carly@hbkuk.co.uk

      8 Atlanta Boulevard

    Postcode Areas
      RM, IG


                   
    Amountperday
      5

    Amountperweek
      20

    Dateandtimelastlead
      21/05/2006 13:16:34

    holidayperiod
      False



    Sorry i forgot to mention that i need to check that they have not gone over there daily or weekly limit.

    If they do not match any of these details it will go on to the next broker until it find one who fits

    Hope this helps

    Thanks


    Website Designed and Developed by Pablo Varando.