Select distinct question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select distinct question

Author

Topic

bquinn7186
Starting Member

8 Posts

Posted - 2005-04-07 : 09:32:32

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 PM
Hospital1 Critical Care 8 4/6/05 4:24 PM
Hospital1 Med-Surg 3 4/6/05 4:24 PM
Hospital1 Pediatrics 0 4/6/05 4:24 PM
Hospital1 Psychiatry 0 4/6/05 4:24 PM
Hospital1 Telemetry 0 4/6/05 4:24 PM

Hospital1 Cardiology 8 4/7/05 9:04 AM
Hospital1 Critical Care 6 4/7/05 9:04 AM
Hospital1 Pediatrics 4 4/7/05 9:04 AM
Hospital1 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 Updated
Hospital1 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!

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2005-04-07 : 21:05:45

This is sort-of the query you need



select hospname,
SUM(case when deptname = 'Cardiology' THEN Cnt ELSE 0 END) AS Cardiology,
SUM(case when deptname = 'Critical Care' THEN Cnt ELSE 0 END) AS CritCare,
SUM(case when deptname = 'Med-Surg' THEN Cnt ELSE 0 END) AS MedSurg,
SUM(case when deptname = 'Pediatrics' THEN Cnt ELSE 0 END) AS Pediatrics,
SUM(case when deptname = 'Psychiatry' THEN Cnt ELSE 0 END) AS Psychiatry,
SUM(case when deptname = 'Telemetry' THEN Cnt ELSE 0 END) AS Telemetry,
(SELECT MAX(Dt) FROM a) AS LastUpdated
FROM a 
group by hospname

1. I don't know if you need the SUM, MIN, MAX, or whatever other aggregate function. You didn't specify what the numbers represented.
2. If you have many departments, or the departments are dynamic, Rob posted an article on dynamic pivot tables, or do a search on SQLTeam for "pivot table".
3. You need some sort of WHERE clause, possibly WHERE LastUpdated = (SELECT MAX(LastUpdated) FROM A)
4. The LastUpdated column doesn't adhere to the pivot table rules because if it did, it would be a row and not a column. Therefore, it can only be accurate if the LastUpdated is the same for all departments.

Sarah Berger MCSD

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2005-04-07 : 21:16:08

While I'm at it, I'll dump the DDL here, save everyone some fingerwork.


create table a (hospname char(15),deptname char(20),Cnt int,Dt datetime)
insert a values('hospital1', 'Cardiology', 1,'4/6/05 4:24 PM')
insert a values('hospital1', 'Critical Care', 8 ,'4/6/05 4:24 PM')
insert a values('hospital1', 'Med-Surg', 3 ,'4/6/05 4:24 PM')
insert a values('hospital1', 'Pediatrics', 0 ,'4/6/05 4:24 PM')
insert a values('hospital1', 'Psychiatry', 0 ,'4/6/05 4:24 PM')
insert a values('hospital1', 'Telemetry', 0 ,'4/6/05 4:24 PM')
insert a values('hospital1', 'Cardiology', 8 ,'4/7/05 9:04 AM')
insert a values('hospital1', 'Critical Care', 6 ,'4/7/05 9:04 AM')
insert a values('hospital1', 'Pediatrics', 4 ,'4/7/05 9:04 AM')
insert a values('hospital1', 'Psychiatry', 0 ,'4/7/05 9:04 AM')

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources