Dynamic SQL

(Home)
Top 4 Resources
Resource  
Resource  
Resource  
Resource  
Quick Reference
SELECT USE HEDIS


SELECT 
hxp_fname as First_Name, hxp_lname as Last_Name, 
hxp_dob as Pat_DOB,
hxp_subs_id as Member_ID,
hxp_pyr_name as Plan_Number, hxp_receipt_date as Ser_Date,
ReqNo as Accession, 
hxp_bil_acctname as Provider_Name, hxp_clin_abbrv as Client#,
rdr.rd_name as Referring_MD,
adv.test_code as Test_Code,
adv.Analyte_Name as Test_Name, '''' as LOINC, adv.result as Result, adv.units as Units
FROM tblVIPA_Adv_Dec_07 Adv
LEFT OUTER JOIN tblHed_XIF_Patients_Filter xif 
ON Adv.ReqNo = xif.hxp_account_id 
LEFT OUTER JOIN tblHed_XIF_Tests tst
ON Adv.ReqNo = tst.hxt_account_id
AND Adv.test_code = tst.hxt_tcode1
LEFT OUTER JOIN refDrHL rdr
ON Adv.refDr = rdr.rd_code
--WHERE xif.hxp_account_id = '73523162'
--WHERE LEN(tst.hxt_process_id) = 0
DECLARE @VAR set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

USE HEDIS

DECLARE @tblName1 sysname
DECLARE @tblName2 sysname
DECLARE @tblName3 sysname

DECLARE @tblName4 sysname
DECLARE @tblName5 sysname
DECLARE @tblName6 sysname

DECLARE @var1 NVARCHAR(10)
SET @var1 = 'OTH000'

SET @tblName1 = 'tblHed_Xif_Patients'
SET @tblName2 = ''
SET @tblName3 = ''
SET @tblName4 = 'tblHed_Xif_Patient_Upin_Key'
SET @tblName5 = 'tblHed_RefDoc_Upin_Key'
SET @tblName6 = 'tblHed_Rdr_Filter'


IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tblName4) 
EXEC ('DELETE ' + @tblName4) 
ELSE
/*---Create table of unique reqNo's and their upin---*/
EXEC ('CREATE TABLE ' + @tblName4 + 
'(
hxp_account_id nvarchar(15),
hxp_upin_id nvarchar(35)
)')



EXEC ('INSERT INTO ' + @tblName4 + 
' SELECT DISTINCT hxp_account_id, hxp_upin_id
from ' + @tblName1 + 
' GROUP BY hxp_account_id, hxp_upin_id')


/*---create rdr's with a unique upin from home brew table---*/
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tblName5)
EXEC ('DELETE ' + @tblName5)
ELSE
EXEC ('CREATE TABLE ' + @tblName5 +
'(
rdr_upin_id nvarchar(35),
rdr_lname nvarchar(30),
rdr_fname nvarchar(20),
)')


EXEC ('INSERT INTO ' + @tblName5 +
'SELECT rdr_upin_id, rdr_lname, rdr_fname
FROM tblHed_RefDoc
GROUP BY rdr_upin_id, rdr_lname, rdr_fname
HAVING len(rdr_upin_id) > 0 and len(rdr_upin_id) <= 6
AND tblHed_RefDoc.rdr_upin_id <> ''OTH000'' ORDER BY rdr_lname, rdr_fname')



/*---Join tblHed_Xif_Patient_Upin_Key to tblHed_RefDoc_Upin_Key---*/
/*---And dump results into tblHed_Rdr_Filter---*/
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tblName6)
EXEC ('DELETE ' + @tblName6)
ELSE
EXEC ('CREATE TABLE ' + @tblName6 +
'(
rdr_account_id nvarchar(15),
rdr_upin_id nvarchar(35),
rdr_lname nvarchar(30),
rdr_fname nvarchar(20),
)')

EXEC ('INSERT INTO ' + @tblName6 + 
' SELECT DISTINCT hxp_account_id, hxp_upin_id, rdr_lname, rdr_fname
FROM tblHed_RefDoc_Upin_Key 
INNER JOIN tblHed_Xif_Patient_Upin_Key 
ON rdr_upin_id = hxp_upin_id
GROUP BY hxp_account_id, hxp_upin_id, rdr_lname, rdr_fname
ORDER BY hxp_upin_id')



SELECT * FROM tblHed_Rdr_Filter
IF EXISTS IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'tblHed_RefDoc_Filter')
DROP TABLE tblHed_RefDoc_Filter
GO
USE HEDIS
CREATE TABLE tblHed_RefDoc_Filter
(
--rdr_upin_id nvarchar(35),
rdr_srcfac nvarchar(15),
rdr_refdr nvarchar(15),
rdr_lname nvarchar(30),
rdr_fname nvarchar(20),
)
INSERT INTO tblHed_RefDoc_Filter
SELECT distinct rdr_srcfac, rdr_id, '' as rdr_lname, '' as rdr_fname
--, rdr_lname, rdr_fname
FROM tblHed_RefDoc
--GROUP BY rdr_upin_id, rdr_srcfac, rdr_id, rdr_lname, rdr_fname
--HAVING len(rdr_upin_id) > 0
ORDER BY rdr_lname, rdr_fname
Errors