insert to @ variables then joining them

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)

insert to @ variables then joining them

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-05-04 : 11:09:53

im trying to learn something new, so please be kind....
if i insert values into the blue table
and
insert values into the red table
how do i join them properly to retrieve values to one table, not sure of the syntax. what is my next step?
do i select * from one table then insert to a temp table then insert ??? confused.....

--***************************
--DETERMINE NURSING OB TRIAGE NOTE
--***************************
DECLARE @nurseob Table
(ClientvisitGUID numeric(16,0),
mrn varchar(20),
cat_name varchar(2000),
time_in_bed datetime,
time_disposition datetime,
disposition varchar(2000))

Insert into @nurseob
SELECT DISTINCT
cv.GUID AS ClientVisitGUID,
cv.idcode as mrn,
obsCatalog.Name as cat_name,
case when obsCatalog.Name='nsg_OBTriageHx_AdmData_arrival_time' then
ISNULL(obser.valuetext,[DBO].[NYP_AddObsValue_Fn] (cv.guid, obserdoc.ObservationDocumentGUID)) end AS time_in_bed,
case when obsCatalog.Name='nsg_OBTriageHx_pt_disposition_dt1' then
ISNULL(obser.valuetext,[DBO].[NYP_AddObsValue_Fn] (cv.guid, obserdoc.ObservationDocumentGUID)) end AS time_disposition,
case when obsCatalog.Name='nsg_OBTriageHx_pt_disposition' then
ISNULL(obser.valuetext,[DBO].[NYP_AddObsValue_Fn] (cv.guid, obserdoc.ObservationDocumentGUID)) end AS disposition
FROM
dbo.CV3ClientVisit AS cv WITH (nolock)
INNER JOIN
dbo.CV3Location AS loc WITH (nolock) ON cv.CurrentLocationGUID = loc.GUID
AND loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001')
INNER JOIN
dbo.CV3ClientDocument AS cd WITH (nolock) ON cv.GUID = cd.ClientVisitGUID AND cv.TypeCode = 'Inpatient' AND
cd.PatCareDocGUID IN ('9000001670102020', '4000001085202001') AND cv.ClientGUID = cd.ClientGUID
INNER JOIN
dbo.CV3ObservationDocument AS obserdoc WITH (nolock) ON cd.GUID = obserdoc.OwnerGUID
INNER JOIN
dbo.CV3Observation AS obser WITH (nolock) ON obser.GUID = obserdoc.ObservationGUID
INNER JOIN
dbo.CV3ObsCatalogItem AS obscatitem WITH (nolock) ON obscatitem.GUID = obser.ObsItemGUID
INNER JOIN
dbo.CV3ObsCatalogMasterItem AS obsCatalog WITH (nolock) ON obsCatalog.GUID = obscatitem.MasterObsGUID AND
obsCatalog.Name IN ('nsg_OBTriageHx_AdmData_arrival_time', 'nsg_OBTriageHx_pt_disposition_dt1', 'nsg_OBTriageHx_pt_disposition')
left outer JOIN
dbo.SCMObsFSListValues AS obsFS WITH (nolock) ON obsFS.ParentGUID = obserdoc.ObservationDocumentGUID

group BY
cv.GUID,
cv.idcode,
cv.visitidcode,
obsCatalog.Name,
obser.ValueText,
obsFS.Value,
obserdoc.ObservationDocumentGUID

--************************************
--DETERMINE OB TRIAGE ALLEN NOTE
--************************************
DECLARE @oballen Table
(ClientvisitGUID numeric(16,0),
mrn varchar(20),
cat_name varchar(2000),
TIME_PATIENT_ARRIVAL DATETIME,
TIME_INIT_NURSE_ASSES datetime,
TIME_INIT_PHYS_ASSES datetime,
CHIEF_COMPLAINT varchar(2000),
GESTATIONAL_AGE_WEEKS varchar(2000),
GESTATIONAL_AGE_DAYS varchar(2000))

Insert into @oballen
SELECT DISTINCT

cv.GUID AS ClientVisitGUID,
cv.IDCode AS MRN,
obsCatalog.Name as cat_name,
case when obsCatalog.Name='note_OB_Triage_Admit_ls' then
ISNULL(obser.valuetext,[DBO].[NYP_AddObsValue_Fn] (cv.guid, obserdoc.ObservationDocumentGUID)) end AS TIME_PATIENT_ARRIVAL,
CD.AuthoredDtm AS TIME_INIT_NURSE_ASSES,
CD.AuthoredDtm AS TIME_INIT_PHYS_ASSES,
case when obsCatalog.Name='note_OB_Triage_chiefcomp_ls' then
ISNULL(obser.valuetext,[DBO].[NYP_AddObsValue_Fn] (cv.guid, obserdoc.ObservationDocumentGUID)) end AS CHIEF_COMPLAINT,
case when obsCatalog.Name='note_OB_Delivery_GA_weeks_ft' then
ISNULL(obser.valuetext,[DBO].[NYP_AddObsValue_Fn] (cv.guid, obserdoc.ObservationDocumentGUID)) end AS GESTATIONAL_AGE_WEEKS,
case when obsCatalog.Name='nsg_OBTriageHx_pt_disposition' then
ISNULL(obser.valuetext,[DBO].[NYP_AddObsValue_Fn] (cv.guid, obserdoc.ObservationDocumentGUID)) end AS GESTATIONAL_AGE_DAYS

FROM
dbo.CV3ClientVisit AS cv WITH (nolock)
INNER JOIN
dbo.CV3Location AS loc WITH (nolock) ON cv.CurrentLocationGUID = loc.GUID
AND loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001')
INNER JOIN
dbo.CV3ClientDocument AS cd WITH (nolock) ON cv.GUID = cd.ClientVisitGUID
AND cv.TypeCode = 'Inpatient' and cd.PatCareDocGUID ='1000001197202001'
INNER JOIN
dbo.CV3ObservationDocument AS obserdoc WITH (nolock) ON cd.GUID = obserdoc.OwnerGUID
INNER JOIN
dbo.CV3Observation AS obser WITH (nolock) ON obser.GUID = obserdoc.ObservationGUID
INNER JOIN
dbo.CV3ObsCatalogItem AS obscatitem WITH (nolock) ON obscatitem.GUID = obser.ObsItemGUID
INNER JOIN
dbo.CV3ObsCatalogMasterItem AS obsCatalog WITH (nolock) ON obsCatalog.GUID = obscatitem.MasterObsGUID
and obsCatalog.Name in('note_OB_Triage_chiefcomp_ls','note_OB_Delivery_GA_weeks_ft',
'note_OB_Delivery_GA_days_ft','note_OB_Triage_Admit_ls')
LEFT OUTER JOIN
dbo.SCMObsFSListValues AS obsFS WITH (nolock) ON obsFS.ParentGUID = obserdoc.ObservationDocumentGUID

group BY
Site ,
loc.ParentGUID ,
cv.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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-04 : 11:19:59

You use them just as you would normal tables joining on the appropriate column(s).

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-05-04 : 11:24:15

thanks,
do i just use a where statement below the 2
then do i select the values i want from the two
then insert to a temp table?
then i can run my update query?
the more i question the more i seem to understand.. sorry just need to get this done today.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-05-04 : 14:24:31

Maybe just me but I'm not sure what you are asking. Are you trying to write a stored procedure, run a stored procedure, write a select statement?

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-05-04 : 15:13:24

write a stored proc, but im pretty sure i figured it out

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources