query i cant get my head round...

  • can you help me create this query im having a problem, i cant quite get my head round it.

    i have a view...

    http://img446.imageshack.us/img446/5760/newview8dm.jpg

    the columns im interested in are

    ConsultancyID(primary key)

    ConsultancyBookingNumber (unique per booking. there are several rows for each ConsultancyBookingNumber, but each one has a different ConsultancyStateID)

    ConsultancyStateID (step at which the booking is at. 1 just began, 10 confirmed)

    ConsultancyID...ConsultancyBookingNumber...ConsultancyStateID

    1.......................1............................................1

    2.......................1............................................2

    3.......................1............................................7

    4.......................2............................................1

    5.......................2............................................3

    6.......................3............................................1

    7.......................3............................................4

    I have a simple query

    SELECTdistinct(consultancybookingnumber),max(consultancystateid)

    FROM ben_vw_consultancy_history

    GROUP BY consultancybookingnumber

    which outputs one row per ConsultancyBookingNumber, and this row has the highest ConsultancyStateID for that ConsultancyBookingNumber.

    The query outputs rows 3, 5 and 7 (correctly)

    i need to alter the query to output only the corresponding ConsultancyID of the above rows, and also to exclude a booking number if the highest ConsultancyStateID for it is 4.

    so in the above example the new query will only output the ConsultancyIDs 5 + 3.

    i hope this makes sense!

    any help is appreciated,

    ben

  • Select q.consultancybookingnumber, q.ConsultancyStateID, h.ConsultancyID

    from

        (select consultancybookingnumber, max(consultancystateid) as ConsultancyStateID

         from ben_vw_consultancy_history

         group by consultancybookingnumber

        ) q

        join  ben_vw_consultancy_history  h

          on  q.consultancybookingnumber = h.consultancybookingnumber

          and q.ConsultancyStateID = h.ConsultancyStateID

    hth

     


    * Noel

  • If I'm reading this right...

    You don't need the DISTINCT statement, the GROUP handles that.

    To filter on aggregates after the grouping, use the HAVING clause:

    SELECT hist.ConsultancyID
      FROM  ben_vw_consultancy_history hist INNER JOIN
            (SELECT ConsultancyBookingNumber, 
                    MAX(ConsultancyStateID) AS [ConsultancyStateID]
               FROM ben_vw_consultancy_history
              GROUP BY consultancybookingnumber
             HAVING MAX(ConsultancyStateID) != 4) agg 
                    ON hist.ConsultancyBookingNumber = agg.ConsultancyBookingNumber AND
                       hist.ConsultancyStateID = agg.ConsultancyStateID

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • thanks for the help, im used Eddie Wuerch's suggestion and it works great!

    ben

  • You are correct! I forgot about the Filtering of ConsultancyID

    It happens to me when doing multiple stuff at once

    Cheers,

     

      


    * Noel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply