Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: Select Statement

    I have a small issue with a select statement. Here is some sample data to look at and understand what I am going to explain.

    batch_numb date_prodtn item_code fpo_number complete
    ---------- --------------------------- ------------ ---------- --------
    093737 2002-01-31 00:00:00 PFB603S9055 017862 TES
    093738 2002-01-31 00:00:00 PFB603S9055 017862 YES
    093739 2002-01-31 00:00:00 PFB603S9055 017862 YES
    093740 2002-01-31 00:00:00 PFB603S9055 017862 YES
    093741 2002-01-31 00:00:00 PFB603S9055 017862 YES
    093742 2002-01-31 00:00:00 PFB603S9055 017862 YES
    093743 2002-01-31 00:00:00 PFB603S9055 017862 YES
    093744 2002-01-31 00:00:00 PFB603S9055 017862 NULL
    093745 2002-01-31 00:00:00 PFB603S9055 017862 YES
    093746 2002-01-31 00:00:00 PFB603S9055 017862 YES
    393745 2002-01-31 00:00:00 PFB603S9055 017862 NULL


    I want to select only fpo's that have the complete column = yes. The thing about this is that I only want it to be selected it if all fpo's with that fpo number are have the complete column = yes. Because an fpo contains multiple batches. Unless every batch is complete the fpo is not complete. All batches need to be complete to have an fpo complete.

    Thats why I need to be able to select only fpos with the same fpo number = yes in the complete column.

    If you look at this information you can tell that this fpo # is not complete because under coplete you have a few nulls.

    This table contains 1000's of fpo so I can't provide the fpo number for each to check if it is complete.

    How would I be able to approach this issue.

    All help is appreciated.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    I think you'll have to break it into two queries instead of one. Perhaps you could create a temporary table inwhich all fpo's are insterted that have the complete = 'yes' value. Then delete from the temp. those with null.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    SELECT fpo_number
       FROM the_table
       GROUP BY fpo_number
       HAVING Count(*) = Sum(CASE WHEN 'YES' = complete THEN 1 END)
    -PatP

  4. #4
    Join Date
    Jan 2004
    Posts
    164
    Thanks alot. The count statement did exactly what I was wanting it to do. It only selected the fpo if all the batches that pertained to that fpo where complete.

    Thanks again!

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    Pat: neat!

Posting Permissions

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