July 15, 2013 at 10:23 am
Hello and thank you for your time. I feel like this should be simple but I can’t seem to work it out….maybe I have been working on it so long that I need another perspective.
I have a Lab_result_obr_p table that contains labs that have been resulted: The PK is LabNum which is the lab Number and the FK is OrderNum which is the order number.
So in this table one order# can have multiple lab#s
Table: Lab_result_obr_p:
LabNum OrderNum
A29E02FED2C6DDA8
4A563D24D2C6DDA8
0F53BC60DC01E4EB
GS43A689DC01E4EB
F943C7034DF2654G7
The other table is Lab_rsults_obx and it contains the lab components and individual component results. In this table, each lab# (uique_obr_Number) from above will have multiple lab comments and results
Table: Lab_rsults_obx
LabNem LabC Flag Value
A29E02FEWBC N 3.5
A29E02FERBC N 260
4A563D24LMP: N 50
4A563D24RH TYPE N DNR
0F53BC60BACTERIA N TNP
GS43A689MCV N 30
GS43A689MCH N 40
F943C7034RH TYPE Y Negative
I need to select all ngn_order_num from Lab_result_obr_p where all components of all labs has a Abnormal_Flag of N and does not have a value of TNP. So if an Order has two labs, I need all the components for both labs to have an N for Abnormal_Flag and to not have a value of TNP for the order number to be selected
So for the data above my result would look like:
ngn_order_num
D2C6DDA8
July 15, 2013 at 10:39 am
Total guess but here is two ways that might work.
select *
from Lab_result_obr_p o
left join Lab_rsults_obx r on o.LabNum = r.LabNem and r.Flag = 'N' and Value <> 'TNP'
where re.LabNem is null
select *
from Lab_result_obr_p o
WHERE NOT EXISTS( SELECT 1 FROM Lab_rsults_obx r WHERE o.LabNum = r.LabNem and r.Flag = 'N' and Value <> 'TNP')
If you need more detailed help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2013 at 11:23 am
Thank you for your reply and I will read your article about posting
However, both examples fail to evaluate all records in the related table before returning the OrderNum
If an OrderNum has 2 #LabNum's each with 10 componets, I need all 10 components from both LabNum's to satisfy the conditions before returning the OrderNum. I have tried every exists combination that I know of but it never accounts for all components. I believe it grabs the first case that meets the condition instead of evaluating them all.
I will post the additional scripts you suggested soon
Thanks
July 15, 2013 at 12:32 pm
chrissorric (7/15/2013)
Thank you for your reply and I will read your article about postingHowever, both examples fail to evaluate all records in the related table before returning the OrderNum
If an OrderNum has 2 #LabNum's each with 10 componets, I need all 10 components from both LabNum's to satisfy the conditions before returning the OrderNum. I have tried every exists combination that I know of but it never accounts for all components. I believe it grabs the first case that meets the condition instead of evaluating them all.
I will post the additional scripts you suggested soon
Thanks
Yeah those are the kinds of things that are hard to see when coding blind. 😉 Once you post some ddl and sample data we can knock this out.
You could possibly change the subquery in the exists version to something like this:
select top 1
FROM Lab_rsults_obx r
WHERE o.LabNum = r.LabNem
order by case when Flag = 'N' and Value <> 'TNP then 1 else 0 end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2013 at 1:56 pm
select distinct OrderNum as ngn_order_num
from Lab_result_obr_p o
join Lab_rsults_obx r on o.LabNum = r.LabNem and r.Flag = 'N' and Value <> 'TNP'
where OrderNum not in
(select distinct OrderNum as ngn_order_num
from Lab_result_obr_p o
join Lab_rsults_obx r on o.LabNum = r.LabNem and r.Flag = 'N' and Value = 'TNP')
-- OR depending on how you want to evaluate Value = 'TNP'
select distinct OrderNum as ngn_order_num
from Lab_result_obr_p o
join Lab_rsults_obx r on o.LabNum = r.LabNem and r.Flag = 'N' and Value <> 'TNP'
where OrderNum not in
(select distinct OrderNum as ngn_order_num
from Lab_result_obr_p o
join Lab_rsults_obx r on o.LabNum = r.LabNem and Value = 'TNP')
July 15, 2013 at 2:05 pm
Seeing as I work in medical these tables look a lot like NextGen tables 😉
July 17, 2013 at 10:13 am
Ha....got to love NextGen!!
July 17, 2013 at 10:16 am
Thanks for all the help!
Edit....Out of all the offerings, this one seemed to work the best when testing
DECLARE@Orders TABLE
(
LabNum VARCHAR(9) NOT NULL,
OrderNum CHAR(8) NOT NULL
);
INSERT@Orders
(
LabNum,
OrderNum
)
VALUES('A29E02FE', 'D2C6DDA8'),
('4A563D24', 'D2C6DDA8'),
('0F53BC60', 'DC01E4EB'),
('GS43A689', 'DC01E4EB'),
('F943C7034', 'DF2654G7');
DECLARE@Results TABLE
(
LabNum VARCHAR(9) NOT NULL,
Lab_C VARCHAR(8) NOT NULL,
Flag CHAR(1) NOT NULL,
Value VARCHAR(8) NOT NULL
);
INSERT@Results
(
LabNum,
Lab_C,
Flag,
Value
)
VALUES('A29E02FE', 'WBC', 'N', '3.5'),
('A29E02FE', 'RBC', 'N', '260'),
('4A563D24', 'LMP:', 'N', '50'),
('4A563D24', 'RH TYPE', 'N', 'DNR'),
('0F53BC60', 'BACTERIA', 'N', 'TNP'),
('GS43A689', 'MCV', 'N', '30'),
('GS43A689', 'MCH', 'N', '40'),
('F943C7034', 'RH TYPE', 'Y', 'Negative');
-----Via ScottPletcher------------------
SELECTp.OrderNum
FROM@Orders AS p
INNER JOIN@Results AS x ON x.LabNum = p.LabNum
GROUP BYp.OrderNum
HAVING SUM(Case when x.Flag = 'N'THEN 1 ELSE 0 END)= COUNT (x.Flag) and
Max(CASE WHEN x.Value = 'TNP' THEN 1 ELSE 0 END) = 0
July 17, 2013 at 1:11 pm
chrissorric (7/17/2013)Thanks for all the help!
This is what I finally came up with
SELECTp.OrderNum
FROM@Orders AS p
INNER JOIN@Results AS x ON x.LabNum = p.LabNum
GROUP BYp.OrderNum
HAVING SUM(Case when x.Flag = 'N'THEN 1 ELSE 0 END)= COUNT (x.Flag) and
Max(CASE WHEN x.Value = 'TNP' THEN 1 ELSE 0 END) = 0
Interesting ... that is exactly the HAVING logic that I posted on the "SQL Team Forum" site in response to requestor "csorric". But I'm glad "you finally came up with" something that worked well for you.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 17, 2013 at 2:04 pm
I have corrected my English above Sir.
My Script has your name and Link to the post in the documentation. I posted here so that somebody else could find the answer if they needed. I appreciate your help and will be much more cognizant of documenting credit
July 17, 2013 at 7:51 pm
A simple join seems to do the trick as well. It seems that I get TWO orders that meet the required condition though. Maybe you are just looking for the TOP(1)?
SELECT DISTINCT
p.OrderNum
--,r.LabNum
--,r.Lab_C
--,r.Flag
--,r.Value
FROM
@Orders AS p
INNER JOIN
@Results AS r
ON p.LabNum = r.LabNum
WHERE
r.Flag = 'N'
AND r.Value <> 'TNP'
July 17, 2013 at 8:59 pm
Voide (7/15/2013)
Seeing as I work in medical these tables look a lot like NextGen tables 😉
Dunno much about NextGen but "OBR" and "OBX" are sure indicators that HL7 is involved 🙂
July 18, 2013 at 7:13 am
SELECT DISTINCT
p.OrderNum
--,r.LabNum
--,r.Lab_C
--,r.Flag
--,r.Value
FROM
@Orders AS p
INNER JOIN
@Results AS r
ON p.LabNum = r.LabNum
WHERE
r.Flag = 'N'
AND r.Value <> 'TNP'
I orginally created a report using just this simple join. But when I was testing the data, it doesn't account for orders having mulitple labs. ORder# DC01E4EB has two Labs ('0F53BC60','GS43A689'). Lab # GS43A689 has two componets (Lab_C) both of which pass the conditions, but Lab #0F53BC60 has one componet and it contains 'TNP' and does not meet both the conditions. The only Order that meets all conditions is 'D2C6DDA8'
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply