August 28, 2012 at 8:20 am
I have a table with some Medication information (I didn't create it).
One row has the Med Name another row has the Med Status (New, Continue Taking, etc.).
I can't figure out a relationship between the two rows to link them.
The only thing I can think of is the first 7 characters of the Obsv_Cd.
I'm pretty sure I need to link the table with an Alias table of itself. ('o' for observation and 's' for status)
I believe I need something like this:
WHERE Left(o.Obsv_Cd, 7) = Left(s.Obsv_Cd,7) where s.obsv_cd_name = 'Status'
I'm struggling with the syntax.
I want to return:
MedName, Status
Any insight would be greatly appreciated!
Here's my test table & data:
Hmm, not sure why I can't add the code. I'm attaching a file with the table def/data.
August 28, 2012 at 8:50 am
Hi
Do you mean something like this:
WITH MED
AS
(
SELECT
*
,LEFT(obsv_cd,7) Med
FROM
#JeffTest
WHERE
obsv_cd_name <> 'Status'
)
,
STAT
AS
(
SELECT
*
,LEFT(obsv_cd,7) Med
FROM
#JeffTest
WHERE
obsv_cd_name = 'Status'
)
SELECT
M.obsv_cd
,M.dsply_val AS 'MedName'
,S.dsply_val AS 'MedStatus'
FROM MED AS M
INNER JOIN STAT AS S
ON M.Med = S.Med
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
August 28, 2012 at 8:59 am
Thank You Andy, that does give me the desired outcome.
I apologize but I left out one detail.
There are other Obsv Codes that begin with A_Med1 etc. (like Dosage, Frequency etc.)
I guess I could just change the where clause of the first table to get past that issue. (to catch all the Med Names)
August 28, 2012 at 9:08 am
Another possibility:
--===============================================================================================
-- Test data:
--===============================================================================================
drop table dbo.Medication;
create table dbo.Medication
(
obsv_cd varchar(30),
obsv_cd_name varchar(10),
med_name varchar(30),
med_status varchar(30)
);
insert into dbo.Medication values ( 'ABCDEFG111', '', 'Name1', '' );
insert into dbo.Medication values ( 'ABCDEFG111', 'Status', '', 'New' );
insert into dbo.Medication values ( 'ABCDEFH111', '', 'Name2', '' );
insert into dbo.Medication values ( 'ABCDEFH111', 'Status', '', 'Continue Taking' );
select * from dbo.Medication;
GO
--===============================================================================================
-- Procedure:
--===============================================================================================
select MED.obsv_cd, MED.med_name, X.med_status
from dbo.Medication MED
cross apply (select med_status from dbo.Medication where left(obsv_cd,7) = left(MED.obsv_cd,7)
and obsv_cd_name = 'Status') X
where obsv_cd_name <> 'Status'
August 28, 2012 at 9:24 am
Probably better ways of doing, sure someone will improve on this!
WITH MED
AS
(
SELECT
*
,LEFT(obsv_cd,7) Med
FROM
#JeffTest
WHERE
obsv_cd_name <> 'Status'
AND RIGHT(obsv_cd,4) = 'Name'
)
,
STAT
AS
(
SELECT
*
,LEFT(obsv_cd,7) Med
FROM
#JeffTest
WHERE
obsv_cd_name = 'Status'
AND RIGHT(obsv_cd,6) = 'Status'
)
SELECT
M.obsv_cd
,M.dsply_val AS 'MedName'
,S.dsply_val AS 'MedStatus'
FROM MED AS M
LEFT JOIN STAT AS S
ON M.Med = S.Med
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
August 28, 2012 at 9:25 am
Thanks for your response Laurie.
That is how I was initially attempting to get it done but I was struggling with the Cross Apply.
I appreciate your method.
August 28, 2012 at 9:26 am
rothj (8/28/2012)
Thank You Andy, that does give me the desired outcome.I apologize but I left out one detail.
There are other Obsv Codes that begin with A_Med1 etc. (like Dosage, Frequency etc.)
I guess I could just change the where clause of the first table to get past that issue. (to catch all the Med Names)
Not quite sure where these Obsv Codes are: If on one of the two records referred to in my solution, you can just add the correct column to a) the select list, or b) the cross apply table & then add it to the select list.
If in another record, you can add another cross apply to extract it.
August 28, 2012 at 9:27 am
rothj (8/28/2012)
Thanks for your response Laurie.That is how I was initially attempting to get it done but I was struggling with the Cross Apply.
I appreciate your method.
Post your cross apply method & we can have a look at it.
If you could post some sample data for a couple of patients, that would help.
August 28, 2012 at 9:31 am
Your example got me passed my error.
I'm good now.
THANKS!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply