SQL Query By Date

I have a table with the following structure: FacilityID, DepartmentID, NumberBeds, DateCreated. I record gets added per FacilityID-DepartmentID. Not every facility and every department within the facility needs to have an entry and there can me multiple entries on the same date. I want to create a pivot table of sorts, which I think I can do, as well as the joins to get the Facility Name and Department Name from other tables.

What I really am having problems figuring out right now is how to pull the last entered date per FacilityID-DepartmentID. Here is a sample of what the data looks like in the database and what I want to pull:

Hospital1    Cardiology       1    4/6/05 4:24 PMHospital1    Critical Care     8    4/6/05 4:24 PMHospital1    Med-Surg        3    4/6/05 4:24 PMHospital1    Pediatrics         0    4/6/05 4:24 PMHospital1    Psychiatry         0    4/6/05 4:24 PMHospital1    Telemetry          0    4/6/05 4:24 PM  Hospital1    Cardiology       8       4/7/05 9:04 AMHospital1    Critical Care    6       4/7/05 9:04 AMHospital1    Pediatrics         4       4/7/05 9:04 AMHospital1    Psychiatry        0       4/7/05 9:04 AM

I need to have as output (items in red above):Hospital   Card  CritCare  Med-Surg  Peds  Psych   Telem     Last UpdatedHospital1   8          6                -           4          0         -         4/7/05 9:04 AM

I'm truly stumped on this one and any help, examples or links to documentation would be greatly appreciated!