November 4, 2015 at 4:30 am
I like writing concise and compact sql code without cursors if possible. My current dilemma has me stuck though.
I have 3 tables, but one of them is optionally used and contains a key element of TimeOut to determine which Anesthesia CrnaID to use. It is the optionally used part that has me stumped.
Surgery table
CaseID
Patient
(Sample data: 101,SallyDoe 102,JohnDoe)
Anesthesia table
CaseID
CrnaID
(Sample data:
101,Melvin
102,Bart
102,Jack)
AnesthesiaTime table (this table is optionally used, only if the crna's take a break on long cases)
CaseID
CrnaID
TimeIn
TimeOut
(Sample data:
102,Jack,0800,1030
102,Bart,1030,1130
102,Jack,1130,1215)
Select Patient INNER JOIN Anesthesia produced too many case results. So, I figured out there is an AnesthesiaTime table that only gets used if the anesthesia guys take time-outs. That doesn't happen all the time. I could use TOP 1 on the Anesthesia table, but technically I need to read the AnesthesiaTime table and locate the last time and pull that crna, Jack. I'm not sure how to deal with an optional table.
I believe the IF Exists will be pertinent, but not sure of how to build this query. I've tried subquery without success.
Any thoughts on how to solve the optional table problem?
I'm looking simply for the results of
101,Melvin
102,Jack *because he was the last TimeOut person in the time table for the CaseID
many thanks
"I like spaghetti because my house is made of brick."
~Paul Wuerzner on illogicality 2/14/86 - 11/6/11 😎
November 4, 2015 at 5:00 am
Does this give the expected outcome
CREATE TABLE #Surgery
(CaseID int,
Patient VARCHAR(10)
)
INSERT INTO #Surgery VALUES
(101,'SallyDoe'),
(102,'JohnDoe')
CREATE TABLE #Anesthesia
(
CaseIDINT,
CrnaID VARCHAR(10)
)
INSERT INTO #Anesthesia VALUES
(101,'Melvin'),
(102,'Bart'),
(102,'Jack')
CREATE TABLE #AnesthesiaTime
(
CaseID INT,
CrnaID VARCHAR(10),
TimeIn CHAR(4),
TimeOut CHAR(4)
)
INSERT INTO #AnesthesiaTime VALUES
(102,'Jack','0800','1030'),
(102,'Bart','1030','1130'),
(102,'Jack','1130','1215')
;with cte as(
SELECT
s.CaseID,s.patient,a.CrnaID,
row_number() over (partition by at.caseid order by at.timeout desc, timein desc) as rownum
FROM #Surgery S
INNER JOIN #Anesthesia A ON S.CaseID = A.CaseID
LEFT JOIN #AnesthesiaTime AT ON A.CaseID = AT.CaseID and a.CrnaID = at.CrnaID
)
SELECT CaseID, Patient, CrnaID FROM cte WHERE rownum = 1
November 4, 2015 at 9:20 am
syntax:
ROW_NUMBER ( )
OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
Thank you Sir, I have some learning to do.:-)
"I like spaghetti because my house is made of brick."
~Paul Wuerzner on illogicality 2/14/86 - 11/6/11 😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply