Complex Query problems

Hi all,

Yet again, I have a need to solve a complex query related problem.

I'll try and break this down as simple as possible...OK..

I have a table called contracts
-----------------------------
conid - description
-----------------------------
x01 - Area1
x02 - Area2
-----------------------------

and a hospital table, where 'X' amount of hospitals are tied to a given contract.
-----------------------------
hospid - conid
-----------------------------
hosp1 - x01
hosp2 - x01
hosp3 - x02
hosp4 - xo2

then I have patients table, which deals with the journeys patients have made under the hospitals. So I have a patients table like this...
--------------------------------------------
patid - fromhosp - tohosp - cancin - cancout
--------------------------------------------
01 - hosp1 - hosp3 - 2 - NULL
02 - hosp3 - NULL - NULL - NULL
03 - hosp2 - hosp4 - NULL - 3
04 - hosp4 - hosp1 - NULL - 1
-------------------------------------------

OK then, the patients table details a patients journey from a hospital (fromhosp) and to a hospital (tohosp). These both tie in with the Hospitals table (which I think is causing me the problem as there are two foriegn keys linking to the hospitals table). Also in this table we have a cancin and cancout columns. There are for if any cancellations are made (and act as akey to a cancellations table so we can get the cancellations types).

Ok, now for my query requirements . I want my query (if possible) to return return all hospitals grouped by their contract, and for there to be a column in the result set that counts the amount of journeys a hospital has made (transferring patients) were fromhosp and tohosp count as separate journeys and both columns can contain data in one row and represent two journeys (to and from). I also need a column that counts all the cancellations thats were undertaken per hospital (which are, as I previously mentioned, where grouped by there contracts). The NULLs in the cancin and cancout columns means that there were no cancallations, and the NULLs in the fromhosp or tohosp means that a patient was transferred to or from their home.

Hope I have made this clear, if not come back to me.

Thanks

Tryst