example of insterting into variables

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)

example of insterting into variables

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-05-03 : 16:18:07

i have a stored procedure which uses multiple tables, i would like to insert the values i want from each table then tie them together in the final table.
(not sure if this makes any sense)
does any one have a copy of a sp that does that?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-03 : 16:37:58

>> not sure if this makes any sense
ummm
Where do the variables come in?
It sounds like you just want a join.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-05-04 : 09:44:23

basically. i have a stored procedure that inserts values to a temp table and uses 2 nested queries to retrieve values.
then there is an update query to the final table so i can get the data in one row. this query takes a long time to run. im sure there is a more efficent way of doing this. the reason i am using the blue nested query is the data comes from a different note. so when the final statement is run it retreives 2 rows of data instead of one.

--exec dbo.NYP_ld_triage_eff_msrs_rpt '04/01/2011', '04/05/2011'

create PROCEDURE [dbo].[NYP_ld_triage_eff_msrs_rpt]
( @StartDate datetime,
@EndDate datetime)

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN
DECLARE
@start_date datetime,
@end_date datetime

--use convert to mm/dd/yyyy with default Start_time 12:00:00 and default End_time 11:59:59
SELECT @start_date = convert(datetime,convert(varchar(10), @StartDate, 101))
SELECT @end_date = dateadd(ss, -1, dateadd(dd, 1,convert(datetime,convert(varchar(10), @EndDate, 101))))

CREATE TABLE #FINAL
(
Site varchar(10),
Campus varchar(30),
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,
TIME_DISPOSITION DATETIME,
DISPOSITION varchar(2000))

--Get all patients who had a Nursing OB Triage History,Nursing OB Triage History,OB Triage Note - Allen 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.PatCareDocGUID,
cv.IDCode AS MRN,
cv.VisitIDCode AS EncNumber,
cv.ClientDisplayName AS PATIENT,
cv.AdmitDtm AS TIME_PATIENT_ARRIVAL,
obsCatalog.Name as cat_name,
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,
cd.documentname,
O.time_in_bed AS TIME_IN_BED,
O.time_disposition as TIME_DISPOSITION,
O.disposition AS DISPOSITION
into #result
FROM
dbo.CV3ClientVisit AS cv WITH (nolock)
INNER JOIN
dbo.CV3Location AS loc WITH (nolock) ON cv.CurrentLocationGUID = loc.GUID
INNER JOIN
dbo.CV3ClientDocument AS cd WITH (nolock) ON cv.GUID = cd.ClientVisitGUID
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
LEFT OUTER JOIN
dbo.SCMObsFSListValues AS obsFS WITH (nolock) ON obsFS.ParentGUID = obserdoc.ObservationDocumentGUID

join
(SELECT
ClientVisitGUID,
MIN(RequestedDtm) AS min_order_date,
dbo.CV3User.OrderRoleType
FROM dbo.CV3Order with (nolock)
INNER JOIN
dbo.CV3User with (nolock) ON dbo.CV3Order.UserGUID = dbo.CV3User.GUID
where OrderRoleType in ('Resident', 'NP','PA')
GROUP BY ClientVisitGUID,OrderRoleType) x
on x.clientvisitguid=cv.GUID
join
(SELECT DISTINCT
cv.GUID AS ClientVisitGUID,
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.CV3ClientDocument AS cd WITH (nolock) ON cv.GUID = cd.ClientVisitGUID
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
LEFT OUTER JOIN
dbo.SCMObsFSListValues AS obsFS WITH (nolock) ON obsFS.ParentGUID = obserdoc.ObservationDocumentGUID
WHERE
cd.PatCareDocGUID IN ('9000001670102020',--Nursing OB Triage History
'4000001085202001')--Nursing OB Triage History
and obsCatalog.Name in ('nsg_OBTriageHx_AdmData_arrival_time',--time in bed
'nsg_OBTriageHx_pt_disposition_dt1',--time disposition
'nsg_OBTriageHx_pt_disposition')--disposition
AND cv.TypeCode = 'Inpatient'
group BY
cv.GUID,
obsCatalog.Name,
obser.ValueText,
obsFS.Value,
obserdoc.ObservationDocumentGUID)O
on O.clientvisitguid=cv.GUID
WHERE (cd.PatCareDocGUID ='1000001197202001')--OB Triage Note - Allen
AND loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001')
AND cv.TypeCode = 'Inpatient'
and cv.AdmitDtm BETWEEN @START_DATE AND @END_DATE
and obsCatalog.Name in('note_OB_Triage_chiefcomp_ls',-- Chief complaint
'note_OB_Delivery_GA_weeks_ft',-- Gestational Age Weeks
'note_OB_Delivery_GA_days_ft',-- Gestational Age Days
'note_OB_Triage_Admit_ls')--Patient Arrival

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,
cd.documentname,
O.TIME_IN_BED,
O.TIME_DISPOSITION,
O.DISPOSITION

SELECT * FROM #RESULT
--Add all data to the final table
INSERT #Final
( Site,
Campus,
ClientVisitGUID,
PATIENT,
MRN,
EncNumber,
TIME_PATIENT_ARRIVAL,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES)
SELECT
Site,
Campus,
ClientVisitGUID,
PATIENT,
MRN,
EncNumber,
TIME_PATIENT_ARRIVAL,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES
FROM #Result

group by
Site,
Campus,
ClientVisitGUID,
PATIENT,
MRN,
EncNumber,
TIME_PATIENT_ARRIVAL,
TIME_INIT_NURSE_ASSES,
TIME_INIT_PHYS_ASSES

SELECT * FROM #FINAL
-- 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.ClientVisitGUID=r.ClientVisitGUID

UPDATE #Final
SET GESTATIONAL_AGE_WEEKS = R.ChartedValue2
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID
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.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'note_OB_Delivery_GA_days_ft'

UPDATE #Final
SET TIME_IN_BED = R.TIME_IN_BED
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID
--AND r.cat_name2 = 'nsg_OBTriageHx_AdmData_arrival_time'

UPDATE #Final
SET TIME_DISPOSITION= R.TIME_DISPOSITION
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID
--AND r.cat_name2 = 'nsg_OBTriageHx_pt_disposition_dt1'

UPDATE #Final
SET DISPOSITION = R.DISPOSITION
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID
--AND r.cat_name2 ='note_OB_Triage_Admit_ls'

UPDATE #Final
SET CHIEF_COMPLAINT =R.ChartedValue2
FROM #Result r, #Final f
WHERE f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name ='note_OB_Triage_chiefcomp_ls'

--Final select
SELECT DISTINCT
Site,
Campus,
ClientVisitGUID,
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,
ClientVisitGUID,
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_eff_msrs_rpt') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.NYP_ld_triage_eff_msrs_rpt >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.NYP_ld_triage_eff_msrs_rpt >>>'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources