Combine records on two fields - 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

General SQL Server Forums

New to SQL Server Programming

Combine records on two fields

Author

Topic

amervak
Starting Member

8 Posts

Posted - 2007-11-05 : 12:07:37

Hello everyone -
This is my first post to the forum and I'm very new to SQL. I apologize if this is addressed elsewhere.

Here is an example of the results I am getting from my query

AdmissionID, sNurseInit, sSWInit
100, {NULL}, SAE
100, REG , {Null}

Is there a way to combine (merge, join? I don't know the right word) these records so that a single record for the admission is returned?

AdmissionID, sNurseInit, sSWInit
100, REG, SAE

Thanks in advance!
Amy

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-05 : 12:12:47

try using COALESCE. Read up books online for more info.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-11-05 : 12:20:26

You have two rows in your table, with the same AdmissionID, but one with sNurseInit and one with sSWInit?

Are there always two rows? What if there are 3 rows? or more than one row with the same AdmissionID but NOT NULL for one of the column sNurseInit / sSWInit - i.e. what action should be taken if there are multiple possible values?

Assuming none of those edge-conditions then here's one method:


DECLARE @TestData TABLE
(
	AdmissionID	int,
	sNurseInit	varchar(10),
	sSWInit		varchar(10)
)

INSERT INTO @TestData(AdmissionID, sNurseInit, sSWInit)
SELECT 100, NULL, 'SAE' UNION ALL
SELECT 100, 'REG' , Null UNION ALL
SELECT 101, NULL, 'AAA' UNION ALL
SELECT 101, 'BBB' , Null

SELECT	[AdmissionID] = COALESCE(T1.AdmissionID, T2.AdmissionID),
	T1.sNurseInit,
	T2.sSWInit
FROM	@TestData AS T1
	FULL OUTER JOIN @TestData AS T2
		 ON T2.AdmissionID = T1.AdmissionID
		AND T2.sSWInit IS NOT NULL
WHERE	T1.sNurseInit IS NOT NULL

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 13:05:04

Or...
SELECT AdmissionID, MAX(sNurseInit), MAX(sSWInit) FROM Table1
GROUP BY AdmissionID

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-11-05 : 14:14:42

Personally speaking that wouldn't be my first choice because it is liable to disguise any multiple-row data sets.

Kristen

amervak
Starting Member

8 Posts

Posted - 2007-11-05 : 14:29:26

Thanks for the responses. The query pulls hundreds of admissions. For any given admission, there will be only one value for sNurseInit and one value for sSWInit. So, I think Peso's suggestion will work for the time being. I'm going to read up on COALESCE too and work through Kristen's solution. I have never used the COALESCE function.

Thanks again for the help!
Amy

Kristen
Test

22859 Posts

Posted - 2007-11-05 : 14:43:18

"there will be only one value for sNurseInit and one value for sSWInit"

is there a rule that enforces that at the database level?

Should have said earlier, but (albeit given the limited information I have so far) it sounds like a crap design. No offence intended, but its a crap design ...

"I'm going to read up on COALESCE too "

COALESCE alone isn't going to help you overly much. It would be fine if there were two columns within the same row (or within a Parent / Child or 1:1 relationship) that delivered alternative values for a column, but that does not seem to be the case here.

COALESCE returns the first non-NULL parameter, or NULL if all parameters are NULL

Kristen

amervak
Starting Member

8 Posts

Posted - 2007-11-06 : 09:32:42

"No offence intended, but its a crap design ..."

The limitation is not in the database design. It is with my experience and my unfamiliarity with the language I am trying to express my problem through.

Here is the problem from start to finish:

Database Design:

Client Table (Primary Key: ClientID): Holds client-level data (name, dob, gender, etc)

Admission Table (Primary Key: AdmissionID, fK: ClientID): Holds info pertaining to the admission into the program (date, program)

Resource Table (Primary Key: ResourceID, fK: AdmissionID, StaffID): Holds the records of staff assigned to the client for that admission (staffID, discipline, start date, stop date)

Staff Table (Primary Key: StaffID): Holds Staff-level data (staff name, staff initials, employment start date, DOB, etc)

Example: A client was admitted on 10/1/07. There are 3 staff assigned to their care.

Right now, I can get the following result from a query that joins these tables:

AdmissionID, Admit Date, ClientName, Staff Discipline (from Resource Table), Staff Initials
100, '2007-10-01', John Doe, Nurse, BBB
100, '2007-10-01', John Doe, Social Worker, SSS
100, '2007-10-01', John Doe, Physician, ABC

I want to be able to write a report (in crystal reports) that contains all of the staffing information on a single line (along with other information that I don't describe here). So, it would look like this for a list of 100 or so clients:

Client, Nurse, Social Worker, Physician
John Doe, BBB, SSS, ABC
Jane Smith, GEP, JJI, EOW

I thought that creating new variables that held the initials for each staff type and then combining them would work, but apparently that is not a good route to take. Any other suggestions now that you have (I hope) a fuller picture?

Amy

Kristen
Test

22859 Posts

Posted - 2007-11-06 : 10:25:15

OK ... so could you do something like this?

Its a bit over-simplified because it looks like you need to join Resource -> Staff before you can actually get the Staff's Initials, but as a "proof of concept" does it look right?


SELECT A.AdmissionID, ..., R1.StaffInitials AS Nurse, R2.StaffInitials AS Physician
FROM Admission AS A
    JOIN Resource AS R1
         ON R1.AdmissionID = A.AdmissionID
        AND Discipline = 'Nurse'
    JOIN Resource AS R2
         ON R2.AdmissionID = A.AdmissionID
        AND Discipline = 'Physician'

Kristen

amervak
Starting Member

8 Posts

Posted - 2007-11-06 : 14:29:25

This works great, Kristen. I ran into a different issue (two staff of the same discipline being entered inappropriately - the query then returns more than one record), but that's another problem.

Thanks for your help!
Amy

Kristen
Test

22859 Posts

Posted - 2007-11-06 : 17:15:49

"two staff of the same discipline being entered inappropriately "

Doesn't surprise me, and that;s why I wasn't happy with the earlier discussion.

Presumably you can JOIN the other tables to get the extra info you need, if you hit a rock then come back and ask

Kristen

amervak
Starting Member

8 Posts

Posted - 2007-11-07 : 08:24:29

I'm stuck :(

Here's my code:
SELECT

PA.AdmissionID,
PA.PatientID,

( SELECT EMP.EmployeeInitials
FROM Employees EMP
WHERE R1.EmployeeID = EMP.EmployeeID ) AS RN,

( SELECT EMP.EmployeeInitials
FROM Employees EMP
WHERE R2.EmployeeID = EMP.EmployeeID ) AS SW,

( SELECT EMP.EmployeeInitials
FROM Employees EMP
WHERE R3.EmployeeID = EMP.EmployeeID ) AS Chaplain

FROM PatAdmissions PA
JOIN PatResourceAssignments R1 ON R1.AdmissionID = PA.AdmissionID AND R1.DutyID = '001'
JOIN PatResourceAssignments R2 ON R2.AdmissionID = PA.AdmissionID AND R2.DutyID = '003'
JOIN PatResourceAssignments R3 ON R3.AdmissionID = PA.AdmissionID AND R3.DutyID = '012'
WHERE PA.PatientID='600'

And here are the results for one patient where more than one social worker and more than one chaplain were enterd:

AdmissionID, PatientID, RN, SW, Chaplain
22, 600, CSN, AMH, TPM
22, 600, CSN, AMH, ETC
22, 600, CSN, JDK, TPM
22, 600, CSN, JDK, ETC

I want (now) to be able to display ALL of the initials returned on a single line like:
AdmissionID, PatientID, RN, SW, Chaplain
22, 600, CSN, AMH/JDK, TPM/ETC

Is this possible?
Amy

Kristen
Test

22859 Posts

Posted - 2007-11-07 : 08:47:10

In order to get a list of matching Staff Initials you will need to call a function, instead of joining to the Employees table


CREATE FUNCTION dbo.fnEmployeeInitialsList(@AdmissionID int, @DutyID varchar(10))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SELECT @Output = COALESCE(@Output+'/', '') + CONVERT(varchar(20), E.EmployeeInitials)
	FROM	dbo.PatResourceAssignments AS R
		JOIN dbo.Employees AS E
			 ON E.EmployeeID = R.EmployeeID
	WHERE	    R.AdmissionID = @AdmissionID
		AND R.DutyID = @DutyID
	ORDER BY E.EmployeeInitials

	RETURN @Output
END

SELECT	PA.AdmissionID,
	PA.PatientID,
	dbo.fnEmployeeInitialsList(PA.AdmissionID, '001') AS RN,
	dbo.fnEmployeeInitialsList(PA.AdmissionID, '003') AS SW,
	dbo.fnEmployeeInitialsList(PA.AdmissionID, '012')  AS Chaplain
FROM PatAdmissions PA
WHERE PA.PatientID='600'

Kristen

amervak
Starting Member

8 Posts

Posted - 2007-11-08 : 09:50:37

This is resulting in an error (SELECT returns more than one row).
I ran the select statement for a patient that I knew didn't have more than one of any type of staff and it worked. Not so for those with more than one.

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 13:35:00

Can you try just the function please (with an AdmissionID matching PatientID='600' or a similar "multiple-row" key):

SELECT dbo.fnEmployeeInitialsList(SomeAdmissionID, '001')

Maybe try with '003' and '012'

if that gives error (I can't think why it would

) then what does this give?


DECLARE @Output VARCHAR(8000),
	@AdmissionID int, 
	@DutyID varchar(10)

SELECT	@AdmissionID = 123456, 
	@DutyID = '001'

	SELECT @Output = COALESCE(@Output+'/', '') + CONVERT(varchar(20), E.EmployeeInitials)
	FROM	dbo.PatResourceAssignments AS R
		JOIN dbo.Employees AS E
			 ON E.EmployeeID = R.EmployeeID
	WHERE	    R.AdmissionID = @AdmissionID
		AND R.DutyID = @DutyID
	ORDER BY E.EmployeeInitials

	SELECT [@Output] = @Output

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 14:17:34

It seems more and more to be a CROSSTAB/PIVOT report with Staff Discipline as columns, and clients as rows.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 14:22:48

Limited number of column though (so far!) rather than "All row values as columns please" ...

amervak
Starting Member

8 Posts

Posted - 2007-11-08 : 14:50:00

Can you try just the function please (with an AdmissionID matching PatientID='600' or a similar "multiple-row" key):

SELECT dbo.fnEmployeeInitialsList(SomeAdmissionID, '001')
Using a "multiple row"

Kristen - This gave the error. It worked with the single row key though.

I tried the other code and got the same error too.

Peso, I run into the same problem with having a crosstab in crystal reports. The summary field would be employee initials and my choices are limited to a single value (like max or min or count). So, if there were two nurses, I can't get both sets of initials to display.

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 15:33:15

Guys:

Is this some SQL2005 weirdness that I'm missing ??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 15:37:04

You are using SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

amervak
Starting Member

8 Posts

Posted - 2007-11-09 : 09:21:27

And it is now that the full extent of my beginner status is illustrated...
I am using Sybase. I didn't even know that this might make the commands work differently (or, in this case apparently, not at all). Is this the reason it isn't working for me?

If so, sorry for the forum etiquette misstep. I still learned a lot from this topic. Thanks for your kind responses.

Amy

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources