query i cant get my head round...

Question

Post reply

query i cant get my head round...

ben potts

SSC-Addicted

Points: 414
More actions
April 19, 2006 at 2:47 pm

#112764

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

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 2

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

ben potts SSC-Addicted Points: 414 More actions · Answer 3

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

ben

noeld SSC Guru Points: 96590 More actions · Answer 4

You are correct! I forgot about the Filtering of ConsultancyID

It happens to me when doing multiple stuff at once

Cheers,

* Noel