udpate column with null or value from sp temp tbl

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 2008 Forums

Transact-SQL (2008)

udpate column with null or value from sp temp tbl

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-03-07 : 15:58:20

PLEASE REFERENCE IN RED THE FIELD THAT WAS POPULATING THE COLUMN, THE CASE STATEMENT I THOUGHT I COULD USE IN GREEN AND THE UPDATE IN BLUE
i am a bit lost on how to achieve this..i have a sp that updates a temp table column if a certain patient meets the value.
what i need to do is insert NULL when a patient doesnt have the value but has a specific note written.

CREATE TABLE #FINAL
(
Site varchar(10),
Campus varchar(30),
ClientVisitGUID numeric(16,0),
MRN varchar(20),
EncNumber varchar(20),
PATIENT varchar(50),
ADMIT_DATE Datetime,
DISCHARGE_DATE DATETIME,
Unit_Note_Written VARCHAR(30),
LOC_time_rpt varchar(30),
LOC_NAME VARCHAR(60),
ClientDocGUID numeric(16,0),
Value_text VARCHAR(2000),
AUTHOR_DATE DATETIME,
AIRWAY_BREATHING varchar(2000),
IV_FLUIDS varchar(2000),
BLOOD_PRODUCTS varchar(2000),
MEDICATIONS varchar(2000),
LAB_TESTS varchar(2000),
RADIOLOGY_EXAMS VARCHAR(2000),
IV_LINES VARCHAR(2000),
OTHER VARCHAR(2000),
PATIENT_LOC VARCHAR(2000),
RRT_CALLED VARCHAR(2000)
)

--Get all patients who had a Rapid Response Team Responder Note within the select date time
SELECT DISTINCT
Site = 'WEST',
Campus =
CASE
when loc.ParentGUID = 2000001000061001 then 'Allen Hospital'
when loc.ParentGUID = 4000001145061001 then 'Milstein Hospital'
when loc.ParentGUID = 8000001069061001 then 'MS CHONY'
END,
cv.GUID AS ClientVisitGUID,
cv.IDCode AS MRN,
cv.VisitIDCode AS EncNumber,
cv.ClientDisplayName AS PATIENT,
cv.AdmitDtm AS ADMIT_DATE,
cv.DischargeDtm AS DISCHARGE_DATE,
Unit_Note_Written= DBO.nyp_TF_GetLocationForEvent (clientVisitGUID,obserdoc.RecordedDtm),
CV.CurrentLocation AS LOC_TIME_RPT,
LOC.NAME AS LOC_NAME,
cd.GUID AS ClientDocGUID,
ISNULL(obser.ValueText, obsFS.Value) AS Value_TEXT, cd.AuthoredDtm AS AUTHOR_DATE,
obsCatalog.Name,
cd.documentname,
case when obsCatalog.Name = 'note_rapid_resp_recommendations_ft5' then 'lab tests'
when obsCatalog.Name = 'note_rapid_resp_recommendations_ft1' then 'AIRWAY_BREATHING'
when obsCatalog.Name = 'note_rapid_resp_recommendations_ft3' then 'blood products'
when obsCatalog.Name ='note_rapid_resp_recommendations_ft7' then 'iv lines'
when obsCatalog.Name = 'note_rapid_resp_recommendations_ft8' then 'other'
when obsCatalog.Name ='note_rapid_resp_outcomes_ft3' then 'rrt called'
when obsCatalog.Name = 'note_rapid_resp_recommendations_ft2'then 'IV_FLUIDS'
when obsCatalog.Name ='note_rapid_resp_recommendations_ft4' then 'medications'
when obsCatalog.Name = 'note_rapid_resp_recommendations_ft6' then 'radiology'
when obsCatalog.Name = 'note_rapid_resp_pt_loc' then 'loc'
END AS VALUE_TEXT1
/*CASE when obsCatalog.Name <> 'note_rapid_resp_recommendations_ft5' then 'NULL'
when obsCatalog.Name <> 'note_rapid_resp_recommendations_ft1' then 'NULL'
when obsCatalog.Name <> 'note_rapid_resp_recommendations_ft3' then 'NULL'
when obsCatalog.Name <>'note_rapid_resp_recommendations_ft7' then 'NULL'
when obsCatalog.Name <> 'note_rapid_resp_recommendations_ft8' then 'NULL'
when obsCatalog.Name <>'note_rapid_resp_outcomes_ft3' then 'NULL'
when obsCatalog.Name <> 'note_rapid_resp_recommendations_ft2'then 'NULL'
when obsCatalog.Name <>'note_rapid_resp_recommendations_ft4' then 'NULL'
when obsCatalog.Name <> 'note_rapid_resp_recommendations_ft6' then 'NULL'
when obsCatalog.Name <> 'note_rapid_resp_pt_loc' then 'NULL'
END AS VALUE_TEXT2*/

--INTO #Result
FROM CV3ClientVisit cv
INNER JOIN cv3location loc ON cv.currentlocationguid = loc.guid
INNER JOIN CV3ClientDocument cd ON cv.GUID = cd.ClientVisitGUID
INNER JOIN CV3ObservationDocument obserdoc ON cd.GUID = obserdoc.OwnerGUID
INNER JOIN CV3Observation obser ON obser.GUID = obserdoc.ObservationGUID
INNER JOIN CV3ObsCatalogItem AS obscatitem ON obscatitem.GUID = obser.ObsItemGUID
INNER JOIN CV3ObsCatalogMasterItem AS obsCatalog ON obsCatalog.GUID = obscatitem.MasterObsGUID
LEFT OUTER JOIN SCMObsFSListValues AS obsFS WITH (nolock) ON obsFS.ParentGUID = obserdoc.ObservationDocumentGUID

WHERE cd.PatCareDocGUID = '9000001709102020'
AND loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001')
/*AND obsCatalog.Name in ( 'note_rapid_resp_recommendations_ft5','note_rapid_resp_recommendations_ft1',
'note_rapid_resp_recommendations_ft3' ,'note_rapid_resp_recommendations_ft7',
'note_rapid_resp_recommendations_ft8','note_rapid_resp_outcomes_ft3' ,
'note_rapid_resp_recommendations_ft2','note_rapid_resp_recommendations_ft4' ,
'note_rapid_resp_recommendations_ft6' , 'note_rapid_resp_pt_loc')*/

AND cd.AuthoredDtm BETWEEN '12/01/2010' AND '12/31/2010'
ORDER BY cv.GUID

--Add all data to the final table
INSERT #Final
( Site ,
Campus,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
LOC_TIME_RPT,
LOC_NAME,
ClientDocGUID,
AUTHOR_DATE
)
SELECT
Site ,
Campus,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
LOC_TIME_RPT,
LOC_NAME,
ClientDocGUID,
AUTHOR_DATE
FROM #Result

-- Update final table to assign Date and Time for Result Received and Result Communicated (Notification)for each patient
UPDATE #Final
SET AIRWAY_BREATHING = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name = 'note_rapid_resp_recommendations_ft1'

UPDATE #Final
SET IV_FLUIDS = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name ='note_rapid_resp_recommendations_ft2'

UPDATE #Final
SET BLOOD_PRODUCTS = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name = 'note_rapid_resp_recommendations_ft3'
or r.name=''

UPDATE #Final
SET MEDICATIONS = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name ='note_rapid_resp_recommendations_ft4'
or r.name=''

UPDATE #Final
SET LAB_TESTS = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name = 'note_rapid_resp_recommendations_ft5'
or r.name=''

UPDATE #Final
SET RADIOLOGY_EXAMS = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name ='note_rapid_resp_recommendations_ft6'
or r.name=''

UPDATE #Final
SET IV_LINES = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name = 'note_rapid_resp_recommendations_ft7'
or r.name=''

UPDATE #Final
SET OTHER = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name ='note_rapid_resp_recommendations_ft8'
or r.name=''

UPDATE #Final
SET PATIENT_LOC = r.Value_TEXT
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name ='note_rapid_resp_pt_loc'
or r.name=''

UPDATE #Final
SET RRT_CALLED = r.Value_text
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
AND r.name ='note_rapid_resp_outcomes_ft3'
or r.name=''

DELETE FROM #Final
where (AIRWAY_BREATHING IS NULL AND IV_FLUIDS IS NULL AND BLOOD_PRODUCTS IS NULL AND MEDICATIONS IS NULL
AND LAB_TESTS IS NULL AND RADIOLOGY_EXAMS IS NULL AND IV_LINES IS NULL AND OTHER IS NULL AND PATIENT_LOC IS NULL AND RRT_CALLED IS NULL)

--Final select
SELECT DISTINCT
Site ,
Campus,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
LOC_TIME_RPT,
LOC_NAME,
ClientDocGUID,
AUTHOR_DATE,
AIRWAY_BREATHING,
IV_FLUIDS,
BLOOD_PRODUCTS,
MEDICATIONS,
LAB_TESTS,
RADIOLOGY_EXAMS ,
IV_LINES,
OTHER,
PATIENT_LOC,
RRT_CALLED

FROM #Final
ORDER BY MRN

DROP TABLE #Result
DROP TABLE #Final

END

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-03-07 : 17:09:48

Are you trying to update with the literal string 'Null'? Or do you want to update with the value Null. If it is the latter, remove the quotes from around Null.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-03-08 : 08:16:56

i need to populate the columns via the update with a value if a patient has the value or if not it needs to be null or blank.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources