Professional Web Applications Themes

get record based on date time - Coldfusion Database Access

HI Im creating a site that generates leads. When a lead is generated it will select a broker in the list based on certain criteria. The thing i need to do is to select that broker which was used the last. There is a datetime field in my database which is used with the createodbcdatetime function How can i do this Thanx Carly...

  1. #1

    Default get record based on date time

    HI
    Im creating a site that generates leads. When a lead is generated it will
    select a broker in the list based on certain criteria. The thing i need to do
    is to select that broker which was used the last.

    There is a datetime field in my database which is used with the
    createodbcdatetime function

    How can i do this

    Thanx

    Carly

    cybertek23 Guest

  2. #2

    Default Re: get record based on date time

    There isn't enough information in your request for anyone to give you a very
    specific answer. You mentioned that <i>"There is a datetime field in my
    database"</i> which doesn't tell us much. What is the structure of the data
    model, such as is there something like a broker table, and/or a lead table, and
    what is the relationship between the tables, and how do you know which broker
    was used last, and what table contains the datetime field that you mentioned,
    etc.?

    Phil

    paross1 Guest

  3. #3

    Default Re: get record based on date time

    I have two tables broker and lead. They are joined by the field broker ID.

    The date field is in the broker table. Each time a lead is recieved by a
    broker it will insert the date and time which is created by createODBCdatetime
    function. The next time a lead is recieved it will go to the broker who was
    last to recieved a lead

    cybertek23 Guest

  4. #4

    Default Re: get record based on date time

    Something like this will give you the broker ID and name with the most recent
    date.

    SELECT b.id, b.name
    FROM broker b
    WHERE b.datefield = (SELECT MAX(b1.datefield)
    FROM broker b1
    WHERE b1.id = b.id)

    Phil

    paross1 Guest

  5. #5

    Default Re: get record based on date time

    It would make more sense if the date field was in the lead table.
    Dan Bracuk Guest

  6. #6

    Default Re: get record based on date time

    Thanks
    The date field is in both. i need it in the broker to know which broker recieved the last lead
    cybertek23 Guest

  7. #7

    Default Re: get record based on date time

    Thanks

    I dont need the most recent i need the date which is last in the list
    cybertek23 Guest

  8. #8

    Default Re: get record based on date time

    When someone normally says "last" date, most people would assume it to mean the
    most recent, as opposed to "first" which would be the oldest date, etc. So, you
    want the earliest date, so use MIN instead of MAX in the query.

    Phil

    paross1 Guest

  9. #9

    Default Re: get record based on date time

    I am getting the following error

    ODBC Error Code = 07001 (Wrong number of parameters)


    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

    Hint: The cause of this error is usually that your query contains a reference
    to a field which does not exist. You should verify that the fields included in
    your query exist and that you have specified their names correctly.

    My code is

    SELECT b.brokerid, b.contactname
    FROM broker b
    WHERE b.dateandtimelastvisited = (SELECT MIN(b1.dateandtimelastvisited)
    FROM broker b
    WHERE b1.brokerid = b.brokerid)

    I have checked all the names and they are all correct





    cybertek23 Guest

  10. #10

    Default Re: get record based on date time

    Look at your subquery and the table alias. You have it b where it should be b1.

    SELECT b.brokerid, b.contactname
    FROM broker b
    WHERE b.dateandtimelastvisited = (SELECT MIN(b1.dateandtimelastvisited)
    FROM broker <b>b1</b>
    WHERE b1.brokerid = b.brokerid)

    Phil


    paross1 Guest

  11. #11

    Default Re: get record based on date time

    I am now not getting any errors but it is displaying all of my records
    cybertek23 Guest

  12. #12

    Default Re: get record based on date time

    If you are getting all the records, it suggests that they all have the same value in the dateandtimelastvisited field.
    Dan Bracuk Guest

  13. #13

    Default Re: get record based on date time

    Do they all have the same dateandtimelastvisited? Does this work any better?

    SELECT TOP 1 b.brokerid, b.contactname
    FROM broker b
    WHERE b.dateandtimelastvisited = (SELECT MIN(b1.dateandtimelastvisited)
    FROM broker b1
    WHERE b1.brokerid = b.brokerid)
    AND b.dateandtimelastvisited IS NOT NULL

    Phil

    paross1 Guest

  14. #14

    Default Re: get record based on date time

    Hi

    I have added that code but it is only selecting the broker on the top of the
    list Which is the date 21/05/2006 14:14:34

    I have the date fields as broker1 = 21/05/2006 14:14:34 broker 2= 20/04/2006
    08:14:34,

    Thanks

    cybertek23 Guest

  15. #15

    Default Re: get record based on date time

    Sorry, my mistake... you don't need to correlate the two tables on brokerid.

    SELECT TOP 1 b.brokerid, b.contactname
    FROM broker b
    WHERE b.dateandtimelastvisited = (SELECT MIN(b1.dateandtimelastvisited)
    FROM broker b1)
    AND b.dateandtimelastvisited IS NOT NULL

    Phil

    paross1 Guest

  16. #16

    Default Re: get record based on date time

    Thank you so much

    cybertek23 Guest

Similar Threads

  1. CFMX7.0.1 Administrator date time issue showing 13hrsbehind server time
    By bayfield in forum Coldfusion Server Administration
    Replies: 0
    Last Post: December 8th, 04:34 AM
  2. Show record based on time
    By nedflanders in forum Coldfusion Database Access
    Replies: 2
    Last Post: February 17th, 07:35 PM
  3. Replies: 3
    Last Post: October 21st, 07:13 PM
  4. Automatically adding date/time stamp to an edited record
    By Allen Browne in forum Microsoft Access
    Replies: 1
    Last Post: August 1st, 04:19 PM
  5. Is director frame-based or time-based ?
    By Bobby Tan in forum Macromedia Director Lingo
    Replies: 1
    Last Post: July 25th, 11:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139