Results 1 to 3 of 3

Thread: Min Status

  1. #1
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Talking Unanswered: Min Status

    Hello Guys,

    I wonder if anyone can help as my head is about to explode I have a table that lists the various status's that get logged in a job. I want to be able to select the very first status that hit a particualr code and officer name. The only prob is that in some cases a status may be inputted twice (although only supposed to appear once). I am trying to use min to tell me about the first status it sees (and not the second one enetered in erro). Using the below code,

    SELECT DISTINCT

    action_officer.officer_name,
    enquiry_status_log.logged_date,
    enquiry_status_log.log_effective_date,
    enquiry_status_log.enquiry_number,
    enquiry_status_log.enquiry_log_number,

    (select min(enquiry_status_log.enq_status_code)
    from enquiry_status_log
    where enquiry_status_log.enq_status_code IN('48H'))

    FROM
    action_officer,
    enquiry_status_log,
    central_enquiry

    WHERE
    enquiry_status_log.officer_code = 'CBE' AND
    enquiry_status_log.enq_status_code IN('48H') AND
    action_officer.officer_code = enquiry_status_log.officer_code AND
    central_enquiry.enquiry_number = enquiry_status_log.enquiry_number

    but it seems to displaying me both! Is there some obvious I am missing here?

    Thanks

    Lucy

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you try with something like
    Code:
    FROM enquiry_status_log e, ...
    ... AND e.enq_status_code = (SELECT MIN(e1.enq_status_code)
                                 FROM enquiry_status_log e1
                                 WHERE e1.join column = e.join column
                                   AND possibly other join conditions
                                )
    ...

  3. #3
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Min

    Hey, thanks for the post I have been trying out this code as I knew it worked for another table but it is still insisting on bringing me back both status's. Its driving me bonkers as there must be something I am not doing right!

    SELECT

    action_officer.officer_name,
    enquiry_status_log.logged_date,
    central_enquiry.enquiry_number,
    (SELECT
    enquiry_status.enq_status_name
    FROM
    enquiry_status,
    enquiry_status_log
    WHERE
    enquiry_status_log.enquiry_number = central_enquiry.enquiry_number AND
    enquiry_status_log.enq_status_code = enquiry_status.enq_status_code AND
    enquiry_status_log.enquiry_log_number =
    (SELECT MIN(enquiry_status_log.enquiry_log_number)
    FROM
    enquiry_status_log
    WHERE
    enquiry_status_log.enquiry_number = central_enquiry.enquiry_number AND
    enquiry_status_log.enq_status_code IN('48H'))) as status

    FROM
    central_enquiry,
    enquiry_status_log,
    action_officer

    WHERE
    action_officer.officer_code IN('CF') AND
    enquiry_status_log.enq_status_code IN('48H') AND
    action_officer.officer_code = enquiry_status_log.officer_code AND
    enquiry_status_log.enquiry_number = central_enquiry.enquiry_number

    The MIN brings bac the minimum value (log number) where is = '48h'.

Posting Permissions

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