update query to multiple rows, need to one row

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)

update query to multiple rows, need to one row

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-04-22 : 10:42:00

i have the following procedure that feeds a temp table then updates to the final table values based upon specific notes. my problem is that 2 vlaues which come from one note update to a different row, and the other values which come from a different note update to another row. not sure how to get it to update properly. i tried a union query, a nested query.

--exec dbo.NYP_ld_triage_rpt '04/05/2011', '04/15/2011'

CREATE PROCEDURE [dbo].[NYP_ld_triage_rpt]
( @StartDate datetime,
@EndDate datetime)

AS

BEGIN
CREATE TABLE #FINAL
(
Site varchar(10),
Campus varchar(30),
ClientDocGUID numeric(16,0),
ClientVisitGUID numeric(16,0),
PatCareDocGUID numeric(16,0),
PATIENT varchar(50),
MRN varchar(20),
EncNumber varchar(20),
CHIEF_COMPLAINT VARCHAR(2000),
GESTATIONAL_AGE_WEEKS VARCHAR(2000),
GESTATIONAL_AGE_DAYS VARCHAR(2000),
TIME_PATIENT_ARRIVAL Datetime,
TIME_IN_BED DATETIME,
TIME_INIT_NURSE_ASSES DATETIME,
TIME_INIT_PHYS_ASSES DATETIME,
TIME_INIT_ORDERS DATETIME,
OrderRoleType VARCHAR(255),
TIME_DISPOSITION DATETIME,
DISPOSITION varchar(2000),
cat_name VARCHAR(250),
Value_TEXT varchar(2000),
ChartedValue VARCHAR(2000),
ChartedValue2 VARCHAR(2000))

--Get all patients who had a PALLIATIVE CARE CONSULT 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,
cd.GUID as ClientDocGUID,
cd.PatCareDocGUID as PatCareDocGUID,
cv.IDCode AS MRN,
cv.VisitIDCode AS EncNumber,
cv.ClientDisplayName AS PATIENT,
cv.AdmitDtm AS TIME_PATIENT_ARRIVAL,
cv.DischargeDtm AS DISCHARGE_DATE,
CD.DOCUMENTNAME AS NOTE_NAME,
obsCatalog.Name as cat_name,
--ISNULL(obser.ValueText, obsFS.Value) AS Value_TEXT,
--[DBO].[NYP_AddObsValue_Fn] (cv.guid, obserdoc.ObservationDocumentGUID) AS ChartedValue,
ISNULL(obser.valuetext,[DBO].[NYP_AddObsValue_Fn] (cv.guid, obserdoc.ObservationDocumentGUID)) AS ChartedValue2,
CD.AuthoredDtm AS TIME_INIT_NURSE_ASSES,
CD.AuthoredDtm AS TIME_INIT_PHYS_ASSES,
min(X.min_order_date) AS TIME_INIT_ORDERS
INTO #RESULTFROM dbo.CV3ClientVisit AS cv INNER JOIN
dbo.CV3Location AS loc ON cv.CurrentLocationGUID = loc.GUID INNER JOIN
dbo.CV3ClientDocument AS cd ON cv.GUID = cd.ClientVisitGUID INNER JOIN
dbo.CV3ObservationDocument AS obserdoc ON cd.GUID = obserdoc.OwnerGUID INNER JOIN
dbo.CV3Observation AS obser ON obser.GUID = obserdoc.ObservationGUID INNER JOIN
dbo.CV3ObsCatalogItem AS obscatitem ON obscatitem.GUID = obser.ObsItemGUID INNER JOIN
dbo.CV3ObsCatalogMasterItem AS obsCatalog ON obsCatalog.GUID = obscatitem.MasterObsGUID INNER JOIN
dbo.CV3Client as c ON cv.ClientGUID = c.GUID LEFT OUTER JOIN
dbo.SCMObsFSListValues AS obsFS ON obsFS.ParentGUID = obserdoc.ObservationDocumentGUID
join
(SELECT ClientVisitGUID, MIN(RequestedDtm) AS min_order_date,
dbo.CV3User.OrderRoleType
FROM dbo.CV3Order INNER JOIN
dbo.CV3User ON dbo.CV3Order.UserGUID = dbo.CV3User.GUID
where OrderRoleType in ('Resident', 'NP','PA')
GROUP BY ClientVisitGUID,OrderRoleType) x
on x.clientvisitguid=cv.GUID
WHERE --cd.PatCareDocGUID in ('8502020','498102020')-- dev guids
(cd.PatCareDocGUID IN ('9000001670102020',--Nursing OB Triage History
--'4000001085202001',--Nursing OB Triage History
'1000001197202001'))--OB Triage Note - Allen
AND loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001')
AND cv.TypeCode = 'Inpatient' and cv.idcode='285 85 91'
--and cd.AuthoredDtm BETWEEN @Start_Date AND @End_Date
and obsCatalog.Name in('nsg_OBTriageHx_AdmData_arrival_time','nsg_OBTriageHx_pt_disposition_dt1',
'note_OB_Triage_chiefcomp_ls','note_OB_Delivery_GA_weeks_ft',
'note_OB_Delivery_GA_days_ft','note_OB_Triage_Admit_ls')

group BY
Site ,
loc.ParentGUID ,
cv.GUID,
cd.GUID,
cd.PatCareDocGUID,
cv.IDCode,
cv.VisitIDCode,
cv.ClientDisplayName,
cv.AdmitDtm ,
cv.DischargeDtm ,
CD.DOCUMENTNAME,
obsCatalog.Name,
obser.ValueText,
obsFS.Value,
cv.guid,
obserdoc.ObservationDocumentGUID,
obser.valuetext,
CD.AuthoredDtm
select * from #result

--Add all data to the final table
INSERT #Final
( Site,
Campus,
ClientDocGUID,
ClientVisitGUID,
PATIENT,
MRN,
EncNumber,
TIME_PATIENT_ARRIVAL,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES
)
SELECT
Site,
Campus,
ClientDocGUID,
ClientVisitGUID,
PATIENT,
MRN,
EncNumber,
TIME_PATIENT_ARRIVAL,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES
FROM #Result
group by
Site,
Campus,
ClientDocGUID,
ClientVisitGUID,
PATIENT,
MRN,
EncNumber,
TIME_PATIENT_ARRIVAL,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES-- Update final table to assign Date and Time for Result Received and Result Communicated (Notification)for each patient
UPDATE #Final
SET TIME_INIT_ORDERS = R.TIME_INIT_ORDERS
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID

UPDATE #Final
SET GESTATIONAL_AGE_WEEKS = R.ChartedValue2
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID AND r.cat_name = 'note_OB_Delivery_GA_weeks_ft'

UPDATE #Final
SET GESTATIONAL_AGE_DAYS = R.ChartedValue2
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID AND r.cat_name = 'note_OB_Delivery_GA_days_ft'

UPDATE #Final
SET TIME_IN_BED = R.ChartedValue2
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID AND r.cat_name = 'nsg_OBTriageHx_AdmData_arrival_time'

UPDATE #Final
SET TIME_DISPOSITION= R.ChartedValue2
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID AND r.cat_name = 'nsg_OBTriageHx_pt_disposition_dt1'

UPDATE #Final
SET DISPOSITION = R.ChartedValue2
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID AND r.cat_name ='note_OB_Triage_Admit_ls'

UPDATE #Final
SET CHIEF_COMPLAINT =R.ChartedValue2
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID AND r.cat_name ='note_OB_Triage_chiefcomp_ls'
--Final select
SELECT DISTINCT
Site,
Campus,
PATIENT,
MRN,
EncNumber,
CHIEF_COMPLAINT,
GESTATIONAL_AGE_WEEKS,
GESTATIONAL_AGE_DAYS,
TIME_PATIENT_ARRIVAL,
TIME_IN_BED,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES,
min(TIME_INIT_ORDERS) as INIT_ORDER_TIME,
TIME_DISPOSITION,
DISPOSITION

FROM #Final
group BY Site,
Campus,
PATIENT,
MRN,
EncNumber,
CHIEF_COMPLAINT,
GESTATIONAL_AGE_WEEKS,
GESTATIONAL_AGE_DAYS,
TIME_PATIENT_ARRIVAL,
TIME_IN_BED,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES,
TIME_DISPOSITION,
DISPOSITION

DROP TABLE #Result
DROP TABLE #Final

END

IF OBJECT_ID('dbo.NYP_ld_triage_rpt') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.NYP_ld_triage_rpt >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.NYP_ld_triage_rpt >>>'
go

GRANT EXECUTE ON dbo.NYP_ld_triage_rpt TO public;
GRANT EXECUTE ON dbo.NYP_ld_triage_rpt TO crystal
GO

SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources