Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

STUMPED - ERROR - what *is* the problem?

Status
Not open for further replies.
lmctech

lmctech

Programmer
Jan 28, 2002
22
US
I have a SQL stored procedure that I've inherited and I've managed to eliminate all errors but one - and cant see what is causing it. Its driving me NUTS!

Please, take a look at the code below - the error is as follows:
Msg 156, Level 15, State 1, Procedure _Reports_OAMemberCharacteristics_Rule_Savings_G1, Line 1129
Incorrect syntax near the keyword 'SELECT'.



IF EXISTS(
SELECT
1
FROM
SYSOBJECTS
WHERE
NAME = '_Reports_OAMemberCharacteristics_Rule_Savings_G1'
AND
XTYPE = 'P')
BEGIN
DROP PROCEDURE _Reports_OAMemberCharacteristics_Rule_Savings_G1
END
GO

CREATE PROCEDURE _Reports_OAMemberCharacteristics_Rule_Savings_G1

(@i_dAAOD SMALLDATETIME
,@i_iClientID INT
,@i_fConv_Rate FLOAT
)
AS
SET NOCOUNT ON

--Parametes defined
DECLARE
@TargetRefDate DATETIME
, @TargetTracerDate DATETIME
, @FROMDate DATETIME
, @toDate DATETIME
, @FROMDateYear INT
, @toDateYear INT
, @FROMDateYearAdj INT
, @v_iTotalPt_Cnt INT
, @v_iom_Run_ID INT
, @rcID INT
, @ce_databASe_name SYSNAME
, @SQL VARCHAR(MAX)
, @CRLF VARCHAR(5)
, @Quote CHAR(1)


SET @CRLF = CHAR(13) + CHAR(10)

SET @Quote = CHAR(39)

--Get the CE databASe name to read the OA_ref_RuleAttributes Table

SELECT @ce_databASe_name = 'CE'+substring(db_name(),5,8)
--pr INT @ce_databASe_name

--Get OM_Run_ID
SELECT
@v_iom_Run_ID = MAX(om_run_ID)
FROM
Results_RuleProcessing_key
WHERE
AnalysisASOfDate = @i_dAAOD
AND
Client_ID = @i_iClientID
AND
OM_Run_ID IS NOT NULL

--Get total# of eligibility members
SELECT
@v_iTotalPt_Cnt = COUNT(DISTINCT PatientID)
FROM
Mem_Elg
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
EligStart AND EligEnd

--Get the list of Rules for OA report
CREATE TABLE TblRuleList (

RuleCName VARCHAR(200)
,RuleNumber INT
,Client_ID INT
,rcID INT
,bPMH BIT
,OA_CCategory_L1 VARCHAR(50)
,OA_US_Dis_Prevalence DECIMAL (5, 2)
,OA_Perc_Conv DECIMAL(10, 1) DEFAULT 0
,OA_Risk DECIMAL(10, 1) DEFAULT 0
,OA_Event_Cost DECIMAL(10, 2) DEFAULT 0
,OA_Annual_RxCost DECIMAL(10, 2) DEFAULT 0
,o_RuleNumerate INT DEFAULT 0
,o_RuleDenominator INT
,o_#_Mem_CONVERT DECIMAL(10, 1) DEFAULT 0
,o_Expected_AnnSav_Per_Conversion DECIMAL(10,1) DEFAULT 0
,o_Gross_AnnSav DECIMAL(10, 1) DEFAULT 0
,Rpt_Table VARCHAR(50)
)

--INSERTing Rows for Clinical/PMH rules usedIN OA for displaying the Prevalence
SET @SQL = 'INSERT INTO' + @CRLF +
'TblRuleList (' + @CRLF +
' RuleCName' + @CRLF +
', RuleNumber' + @CRLF +
', Client_ID' + @CRLF +
', rcID' + @CRLF +
', bPMH' + @CRLF +
',Rpt_Table)' + @CRLF +
'SELECT' + @CRLF +
' RefOA.OA_RuleCName' + @CRLF +
', rc.RuleNumber' + @CRLF +
', rc.Client_ID' + @CRLF +
', rc.RcID' + @CRLF +
', COALESCE(bPMH, 0)' + @CRLF +
', Rpt_Table = CASE COALESCE(bPMH, 0) ' + @CRLF +
'WHEN 1' + @CRLF +
'THEN ' + @QUOTE + 'Table2' + @QUOTE + @CRLF +
'ELSE ' + @QUOTE + 'Table3' + @QUOTE + ' END' + @CRLF +
'FROM' + @CRLF +
' vRuleCandidates rc' + @CRLF +
'JOIN' + @CRLF +
+ @ce_Database_Name +'.dbo.ref_OA_RuleAttributes RefOA' + @CRLF +
'ON' + @CRLF +
' rc.rcID = refOA.rcID' + @CRLF +
'WHERE' + @CRLF +
' refOA.OA_FORMULARY_FLAG = ' + @QUOTE + 'N' + @QUOTE + ')' + @CRLF
PRINT @SQL
--EXEC(SQL)

--INSERT additional rows for each rules which is used IN calculating the OA cost.
SET @SQL = 'INSERT INTO ' + @CRLF +
' tblRuleList ( ' + @CRLF +
' RuleCName' + @CRLF +
', RuleNumber' + @CRLF +
', Client_ID' + @CRLF +
', rcID' + @CRLF +
', bPMH' + @CRLF +
', Rpt_Table)' + @CRLF +
'SELECT' + @CRLF +
' REFOA.OA_ruleCName' + @CRLF +
', RuleNumber' + @CRLF +
', Client_ID' + @CRLF +
', rc.RcID' + @CRLF +
', COALESCE(bPMH, 0)' + @CRLF +
', ' + @QUOTE + 'Table5' + @QUOTE + ' AS Rpt_Table' + @CRLF +
'FROM' + @CRLF +
'vRuleCandidates rc' + @CRLF +
'JOIN ' + @CRLF +
@ce_Database_Name + '.dbo.ref_OA_RuleAttributes RefOA' + @CRLF +
'ON' + @CRLF +
'rc.rcID = refOA.rcID' + @CRLF +
'WHERE' + @CRLF +
'refOA.OA_Event_Cost IS NOT NULL' + @CRLF

PRINT(@SQL)
--EXEC @SQL

---Get the hardCoded rule attributes FROM the OA reference table.

SET @SQL = 'UPDATE' + @CRLF +
'tblRuleList' + @CRLF +
'SET' + @CRLF +
' OA_CCategory_L1 = refOA.OA_CCategory_L1' + @CRLF +
', OA_US_Dis_Prevalence = refOA.OA_US_Dis_Prevalence' + @CRLF +
', OA_Perc_Conv = refOA.OA_Perc_Conv' + @CRLF +
', OA_Risk = refOA.OA_Risk' + @CRLF +
', OA_Event_Cost = refOA.OA_Event_Cost' + @CRLF +
', OA_Annual_RxCost = refOA.OA_Annual_RxCost' + @CRLF +
',o_Expected_AnnSav_Per_Conversion = refOA.OA_Expected_AnnSav_Per_Conv'
+ @CRLF +
'FROM' + @CRLF +
'tblRuleList RuleList' + @CRLF +
'JOIN' + @CRLF +
@ce_databASe_name + '.dbo.ref_OA_RuleAttributes refOA' + @CRLF +
'ON' + @CRLF +
'RuleList.rcID = refOA.rcID' + @CRLF
PRINT @SQL
--EXEC @SQL

/*************Calculate numeratorAND Denorminator***********/

UPDATE
tblRuleList
SET
o_RuleNumerate = Rst.Yes_Cnt
FROM
tblRuleList RuleList
JOIN

(SELECT
RuleList.rcID
, COUNT(DISTINCT PtKey.PatientID) AS Yes_Cnt
FROM
tblRuleList RuleList
JOIN
Results_RuleProcessing_key RuleKey
ON
RuleList.rcID = RuleKey.rcID
JOIN
Results_RulePatientSUMmary_key PtKey
ON
RuleKey.rpkID = PtKey.rpkID
JOIN
Mem_Elg MemElg
ON
MemElg.PatientID = PtKey.PatientID
WHERE
RuleKey.om_run_ID = @v_iom_Run_ID
AND
PtKey.bN1 = 1
AND
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
GROUP
BY
RuleList.rcID) Rst
ON
RuleList.rcID = Rst.RcID

UPDATE
tblRuleList
SET
o_RuleDenominator = CASE
WHEN bPMH = 1
THEN @v_iTotalPt_Cnt
ELSE Rst.Pop_nCnt END
FROM
tblRuleList RuleList
JOIN

(SELECT
RuleList.rcID
, COUNT(DISTINCT PtKey.PatientID) AS Pop_nCnt

FROM
tblRuleList RuleList
JOIN
Results_RuleProcessing_key RuleKey
ON
RuleList.rcID = RuleKey.rcID
JOIN
Results_RulePatientSUmmary_key PtKey
ON
RuleKey.rpkID = PtKey.rpkID
JOIN
Mem_Elg MemElg
ON
MemElg.PatientID = PtKey.PatientID
WHERE
RuleKey.om_run_ID = @v_iom_Run_ID
AND
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
GROUP
BY
RuleList.rcID
) Rst
ON
RuleList.rcID = Rst.RcID

/***********process table 5: Cost Saving*************************/

--Get the yes criteria drug tracers/Codes for
--the clinical rule usedIN caluclating the Clinical Cost Savings

CREATE TABLE TracerCodes (
rcID INT
, RuleName VARCHAR(80)
, TracerName VARCHAR(50)
, TracerCode VARCHAR(20) not NULL
, Code_Type VARCHAR(5) not NULL
PRIMARY KEY ( rcID
, TracerName
, TracerCode
, Code_Type))

SELECT
@TargetRefDate = GETDATE()
, @TargetTracerDate = GETDATE()
SELECT
@TargetRefDate = MAX(CreateDate)
FROM
vMedispan_Max_CreateDate
WHERE
CreateDate <= @TargetRefDate

DECLARE TracerCursor CURSOR FOR
SELECT
DISTINCT rcid
FROM
tblRuleList
WHERE
OA_Event_Cost > 0.00
AND
OA_Event_Cost IS NOT NULL

OPEN TracerCursor
FETCH NEXT FROM tracerCursor
INTO @rcID

WHILE @@FETCH_STATUS <> -1
BEGIN
DECLARE @StepIDOfFinalCopy INT

SELECT
@StepIDOfFinalCopy = MAX(ASk.stepOrder)
FROM
Vanalysis_Step_Key ask
JOIN
vruleCandidates rc
ON
ask.RuleCName = rc.RuleCName
AND
rc.rcID = @rcID
AND
ask.Client_ID=0
AND
ask.InactiveDate = 65535
JOIN
vAnalysis_Step_Params asp
ON
ask.stepID=asp.stepID
AND
ask.SprocID = 23 --id of a copy step
AND
asp.InactiveDate = 65535

INSERT
INTO
TracerCodes(
rcID
, RuleName
, TracerName
, TracerCode
, Code_Type)
SELECT
DISTINCT
rc.rcID
, rc.RuleCName
, tc.TracerCName
, gpi.NDC
, tcc.Code_Type
FROM
vAnalysis_Step_Key ask
JOIN
vRuleCandidates rc
ON
ask.RuleCName = rc.RuleCName
AND
rc.rcID = @rcID
AND
ask.Client_ID=0
AND
ask.InactiveDate = 65535
JOIN
vAnalysis_Step_Params asp
ON
ask.StepID = asp.stepID
AND
asp.ParamID = 38
AND
asp.InactiveDate = 65535
JOIN
vTracerCandidates tc
ON
asp.ParamValue = tc.TracerCName
JOIN
vTracerCandidateStatusCodes tcsc
ON
tc.StatusCode = tcsc.StatusCode
JOIN
vTracerCandidateCriteria tcc
ON
tc.TracerCname = tcc.TracerCname
AND
tc.RHS_Table = tcc.RHS_Table
JOIN
vREFTAB_MdMdDB gpi
ON
gpi.GPIU_Code LIKE (tcc.PrimaryKeyValue + '%')
AND
gpi.CreateDate = @TargetRefDate
WHERE
ask.StepOrder > @StepIDOfFinalCopy
AND
tcc.RHS_Table = 'G'
AND
tcc.bActive = 1
AND
tcc.client_ID = 0
AND
tcc.CreateDate <= @TargetTracerDate
AND
tcc.InactiveDate > @TargetTracerDate

FETCH NEXT FROM tracerCursor
INTO @rcID
END

CLOSE TracerCursor
DEALLOCATE TracerCursor

---Calculate the Annual Rx Cost of a Drug for the yes criteria of each rule
SET @FromDate = DATEADD(DAY, -366, @i_dAAOD)
SET @ToDate = DATEADD(DAY, -1, @i_dAAOD)
SET @FromDateYear = DATEPART(YEAR,@FromDate)
SET @ToDateYear = DATEPART(YEAR,@ToDate)
SET @FromDateYearAdj = DATEPART(YEAR,(DATEADD(DAY, -90, @FromDate)))

UPDATE
tblRuleList
SET
OA_Annual_RxCost = RxCost.Annual_RxCost
FROM
tblRuleList RuleList
JOIN
(SELECT
tc.RcID
, (SUM(COALESCE(Amount2,0))/(SUM(COALESCE(Days,0)/30.00)) * 12) AS Annual_RxCost
FROM
FL_RecType chrono
JOIN
FL_RxInfo rx
ON
chrono.StartDateYear = rx.StartDateYear
AND
chrono.srcID = rx.SrcID
JOIN
TracerCodes tc
ON
rx.NDC = tc.TracerCode
JOIN
Mem_Elg MemElg
ON
MemElg.PatientID = chrono.PatientID
WHERE
chrono.StartDateYear
BETWEEN
CONVERT(VARCHAR, @FromDateYearAdj) AND CONVERT(VARCHAR, @ToDateYear)
AND
chrono.StartDate < '' + CONVERT(CHAR(8), @ToDate, 112) + N''
AND
chrono.endDate >= ''+ CONVERT(VARCHAR,@FROMDate) + N''
AND
(DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd)
AND
COALESCE(RX.days,0) > 29
GROUP
BY
tc.RcID
) RxCost
ON
RuleList.rcID = RxCost.rcID
WHERE
( RuleList.OA_Annual_RxCost = 0.00
OR
RuleList.OA_Annual_RxCost IS NULL)

--- Calculate the remaining Values AND Update the #tblRuleList table

UPDATE
tblRuleList
SET o_#_Mem_CONVERT =
(COALESCE(o_RuleNumerate,0) * COALESCE(OA_Perc_Conv,0)/100.00)
, o_Expected_AnnSav_Per_Conversion =
(COALESCE(OA_risk,0) * COALESCE(OA_Event_cost,0)/100 - COALESCE(OA_Annual_RxCost,0))

, o_Gross_AnnSav =
((COALESCE(o_RuleNumerate,0) * COALESCE(OA_Perc_Conv,0))/100.00)
* (((COALESCE(OA_risk,0) * COALESCE(OA_Event_cost,0))/100.00)
- COALESCE(OA_Annual_RxCost,0))

FROM
tblRuleList
WHERE
RPT_table = 'Table5'

/**********************************************************
formulary prefernce cost saving
******************************************************/
CREATE TABLE
tbl_TE_TracerDrug_List (
RuleCName VARCHAR(100)
, rcID INT
, TE_Tracer VARCHAR(80)
, DrugName VARCHAR(25)
, DrugLabel VARCHAR(25)
, G_B_IDentifier VARCHAR(1)
)
DECLARE
@v_tbl_EligPt_List TABLE (
rcID INT
, RuleCName VARCHAR(100)
, PatientID VARCHAR(30)
, DrugName VARCHAR(100))

DECLARE
@v_tbl_Drug_Claims TABLE (
RowNo INT IDENTITY(1,1)
, SrcID BIGINT
, TE_Tracer VARCHAR(100)
, DrugName VARCHAR(100)
, PatientID VARCHAR(30)
, AmountPay30Days FLOAT
, Days INT
, G_B_IDentifier VARCHAR(1)
, rcID INT
, RuleCName VARCHAR(100)
)
DECLARE
@v_tbl_Drug_MedianCost TABLE (
TE_Tracer VARCHAR(100)
, DrugName VARCHAR(100)
, Median_30DaysPay FLOAT
, Cnt_30DaysRx FLOAT
, G_B_IDentifier VARCHAR(1)
, rcID INT
, RuleCName VARCHAR(100)
)
DECLARE
@v_tbl_TracerPt_AvgCost TABLE (
TE_Tracer VARCHAR(100)
, PatientID VARCHAR(30)
, B_Avg_30DaysPay FLOAT
, G_Avg_30DaysPay FLOAT
)

DECLARE
@v_SQLStr VARCHAR(1000)
, @v_CE_DBName VARCHAR(20)
, @v_EligPtCnt INT
, @v_Gross_AnnSav FLOAT
, @v_Pref_AvgCostSavingsPerMember FLOAT
, @v_ClientID VARCHAR(10)
, @msYearMonthDay DATETIME

SET @msYearMonthDay=(SELECT
MAX(CreateDate)
FROM
Medispan.dbo.Medispan_Max_CreateDate )

SET @v_ClientID = CONVERT(VARCHAR(10), @i_iClientID)

SET @v_CE_DBName = 'CE' + SUBSTRING(DB_NAME(),5,8)

/************* Get the list of TE_TracerAND DrugNames ***************************************/
SET @SQL =
'INSERT INTO ' + @CRLF +
'tbl_TE_TracerDrug_List (' + @CRLF +
' RuleCName' + @CRLF +
', rcID' + @CRLF +
', TE_Tracer' + @CRLF +
', DrugName' + @CRLF +
', DrugLabel' + @CRLF +
', G_B_IDentifier)' + @CRLF +
'SELECT ' + @CRLF +
'DISTINCT ' + @CRLF +
' RC.RuleCName' + @CRLF +
', RC.rcID' + @CRLF +
', TE.TE_Name AS TE_Tracer' + @CRLF +
', TE.Name' + @CRLF +
', TE.Drug_Label' + @CRLF +
', G_B_IDentifier = CASE' + @CRLF +
'WHEN SUBSTRING(Analysis_Type, 1, 1)' +
'= ' + @QUOTE + 'A' + @QUOTE +
' THEN ' + @QUOTE + 'G' + @QUOTE +
@CRLF +
'WHEN SUBSTRING(Analysis_Type, 1, 1)' +
' = ' + @QUOTE + 'T' + @QUOTE +
' THEN ' + @QUOTE + 'B' + @QUOTE +
@CRLF +
' ELSE ' + @QUOTE + 'UNKNOWN' + @QUOTE +
' END ' + @CRLF +
'FROM ' + @CRLF +
@v_CE_DBName + '.dbo.ref_OA_RuleAttributes RefOA' + @CRLF +
'JOIN' + @CRLF +
'vRuleCandidates RC' + @CRLF +
'ON' + @CRLF +
'RefOA.rcID = RC.rcID' + @CRLF +
'JOIN' + @CRLF +
@v_CE_DBName + '.dbo.vTE TE ' + @CRLF +
'ON' + @CRLF +
'RC.RuleCName = TE.RuleCName' + @CRLF +
'WHERE' + @CRLF +
'SUBSTRING(TE.Analysis_Type, 1, 1)' + @CRLF +
'IN' + @CRLF +
'(''A'', ''T'')' + @CRLF +
'AND' + @CRLF +
'TE.Client_ID = ' + @v_ClientID + @CRLF +
'AND' + @CRLF +
'OA_FORMULARY_FLAG = ' + @QUOTE + 'Y' + @QUOTE + @CRLF
PRINT @SQL
-- EXEC(@SQL)

/**********************get the claims with 30 days Rx cost (do not restrict members who areIN plan*************/
INSERT INTO
@v_tbl_Drug_Claims
SELECT
*
FROM
(SELECT
DISTINCT
RecType.SrcID
, Drug_NDC.TE_Tracer
, Drug_NDC.DrugName
, RecType.PatientID
, (COALESCE(RecType.Amount2, 0)
/ COALESCE(RxInfo.Days, 1)) * 30 AS AmountPay30Days
, COALESCE(RxInfo.Days, 0) AS Days
, Drug_NDC.G_B_IDentifier
, Drug_NDC.rcID
, Drug_NDC.RuleCName
FROM
FL_RecType AS RecType
JOIN
FL_RxInfo AS RxInfo
ON
RecType.SrcID = RxInfo.SrcID
JOIN (
SELECT
Pref_Tracer.TE_Tracer AS TE_Tracer
, Pref_Tracer.DrugName
, MdDb.NDC-UPC-HRI AS NDC
, Pref_Tracer.G_B_IDentifier
, Pref_Tracer.rcID
, Pref_Tracer.RuleCName
FROM
tbl_TE_TracerDrug_List AS Pref_Tracer
JOIN
REFTAB_DrugItems AS MdDb--dbo.vREFTAB_MdMddb AS MdDb
ON
Pref_Tracer.DrugLabel = MdDb.[Product Description Abbreviation].Prod_Label
WHERE
MdDb.msYearMonthDay = @msYearMonthDay
) AS Drug_NDC

ON
RxInfo.NDC = Drug_NDC.NDC
WHERE
RecType.StartDate
BETWEEN
DATEADD(DAY,-366, @i_dAAOD) AND DATEADD(DAY, -1, @i_dAAOD)
AND
COALESCE(RxInfo.Days, 0) >= 30
) AS a
ORDER
BY
a.TE_Tracer
, a.DrugName
, a.AmountPay30Days



/*********** Get the Median cost of TracersAND DrugName ********************************/
--cnt of Rxs is odd #, then get the median cost, if cnt of Rxs is even #, then get the average of middle 2 costs
INSERT INTO
@v_tbl_Drug_MedianCost

SELECT
TE_Tracer
, DrugName
, AVG(AmountPay30Days) AS Median_AmountPay30Days
, Cnt_30dRx
, G_B_IDentifier
, rcID
, RuleCName
FROM
( SELECT
Claims.TE_Tracer
, Claims.DrugName
, Claims.AmountPay30Days
, Claims.Days
, Claims.G_B_IDentifier
, RowIndex.Cnt_30dRx
, Claims.rcID
, Claims.RuleCName
FROM
@v_tbl_Drug_Claims Claims
JOIN
( SELECT
TE_Tracer
, DrugName
, MIN(RowNo) AS Min_RowNo
, MAX(RowNo) AS Max_RowNo
, COUNT(*) AS ClaimsCnt
, SUM(Days) / 30 AS Cnt_30dRx
FROM
@v_tbl_Drug_Claims
GROUP
BY
TE_Tracer
, DrugName
) AS RowIndex

ON
Claims.TE_Tracer = RowIndex.TE_Tracer
AND
Claims.DrugName = RowIndex.DrugName
AND
(claims.RowNo = RowIndex.Min_RowNo + ROUND(ClaimsCnt/2, 0)
OR
claims.RowNo = CASE
WHEN ClaimsCnt%2 = 0
THEN RowIndex.Min_RowNo + ROUND(ClaimsCnt/2, 0) -1
ELSE 0 END)
) AS a
GROUP
BY
TE_Tracer
, DrugName
, Cnt_30dRx
, G_B_IDentifier
, rcID
, RuleCName

/************* Get Eligible members who hit the rules with the triggerring target drug**************/
INSERT INTO
@v_tbl_EligPt_List (
rcID
, RuleCname
, PatientID
, DrugName)
SELECT
DISTINCT
RuleList.rcID
, RuleList.RuleCname
, PtKey.PatientID
, DrugClaim.DrugName
FROM
tbl_TE_TracerDrug_List RuleList
JOIN
Results_RuleProcessing_key RuleKey
ON
RuleList.rcID = RuleKey.rcID
JOIN
Results_RulePatientSUMmary_key PtKey
ON
RuleKey.rpkID = Ptkey.rpkID
JOIN
Results_RulePatientSUMmary_Details PtKey_SrcID
ON
PtKey.rrpskID = PtKey_SrcID.rrpskID
AND
PtKey_SrcID.fieldName = 'srcID'
JOIN
FL_RxInfo RxInfo
ON
PtKey_SrcID.fieldValue = RxInfo.SrcID
JOIN
( SELECT
Pref_Tracer.TE_Tracer AS TE_Tracer
, Pref_Tracer.DrugName
, MdDb.NDC-UPC-HRI AS NDC
, Pref_Tracer.G_B_IDentifier
,Pref_Tracer.rcID, Pref_Tracer.RuleCName
FROM
tbl_TE_TracerDrug_List AS Pref_Tracer
JOIN
REFTAB_DrugItems AS MdDb
ON
Pref_Tracer.DrugLabel = MdDb.[Product Description Abbreviation]
WHERE
MdDb.msYearMonthDay = @msYearMonthDay

) AS DrugClaim
ON
RxInfo.NDC = DrugClaim.NDC
WHERE
COALESCE(RuleKey.om_run_ID, -1) = @v_iom_Run_ID
AND
PtKey.bN1 = 1



/************ Get the Average 30days Rx cost (Brand, Generic) for Tracers / Members *******************/
INSERT INTO
@v_tbl_TracerPt_AvgCost (
TE_Tracer
, PatientID
, B_Avg_30DaysPay
, G_Avg_30DaysPay)
SELECT
DISTINCT
B_Cost.TE_Tracer
, B_Cost.patientID
, B_Cost.B_Avg_30DaysPay
, G_Cost.G_Avg_30DaysPay
FROM
(SELECT
T_MediaCst.TE_Tracer, PtList.PatientID
, AVG(T_MediaCst.Median_30DaysPay) AS B_Avg_30DaysPay
FROM
@v_tbl_EligPt_List PtList
JOIN
@v_tbl_Drug_MedianCost T_MediaCst
ON
PtList.RuleCName = T_MediaCst.RuleCName
AND
PtList.DrugName = T_MediaCst.DrugName
WHERE
T_MediaCst.G_B_IDentifier = 'B'
GROUP
BY
T_MediaCst.TE_Tracer
, PtList.PatientID
) B_Cost
JOIN
(SELECT
TE_Tracer
, AVG(Median_30DaysPay) G_Avg_30DaysPay
FROM
@v_tbl_Drug_MedianCost
WHERE
G_B_IDentifier = 'G'
GROUP
BY
TE_Tracer
) G_Cost
ON
B_Cost.TE_tracer = G_Cost.TE_Tracer

SELECT
@v_Gross_AnnSav = ROUND(@v_EligPtCnt * @i_fConv_Rate * @v_Pref_AvgCostSavingsPerMember,0)
--@v_Gross_AnnSav = ROUND(SUM(AnCost_PerRule) * @i_fConv_Rate, 1) ,
, @v_Pref_AvgCostSavingsPerMember = ROUND(SUM(AnCost_PerRule) / SUM(RulePt_Cnt), 0)
, @v_EligPtCnt = SUM (RulePt_Cnt)
FROM
(SELECT
TE_Tracer
, (AVG(B_Avg_30DaysPay - G_Avg_30DaysPay)*12) * COUNT(*) AS AnCost_PerRule
, COUNT(*) AS RulePt_Cnt
FROM
@v_tbl_TracerPt_AvgCost
GROUP
BY
TE_Tracer
) AS a



/****************************end of formulary prefernce ***************************/

/****************************Start Member Characteristics ***************************/
SELECT
DISTINCT
MemberCharacteristic
, Result
INTO
Member_Characteristics
FROM
(SELECT
'Total Number of Eligible Members' AS MemberCharacteristic
, @v_iTotalPt_Cnt AS Result
UNION
ALL
SELECT
'Male(%)' AS MemberCharacteristic
, ROUND(CONVERT(FLOAT,COUNT(DISTINCT MemElg.PatientID))
/ CONVERT(FLOAT,@v_iTotalPt_Cnt)*100, 1) AS Result
FROM
Mem_Elg MemElg
JOIN
FL_MemInfo MemInfo
ON
MemElg.PatientID = MemInfo.PatientID
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
AND
Sex = 'M'
UNION
ALL
SELECT
'Average Age' AS MemberCharacteristic
, AVG(DATEDIFF(YEAR, Birthdate, @i_dAAOD)) AS Result
FROM (
SELECT
DISTINCT
MemInfo.patientID
, MemInfo.Birthdate
FROM
Mem_Elg MemElg
JOIN
FL_MemInfo MemInfo
ON
MemElg.PatientID = MemInfo.PatientID
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
) AS a
UNION
ALL
SELECT
'Average Number of Significant Co-morbidities *' AS MemberCharacteristic
, CONVERT(FLOAT,pmh_pids)/CONVERT(FLOAT,@v_iTotalPt_Cnt) AS Result
FROM
( SELECT
DISTINCT
SUM(o_RuleNumerate) AS Pmh_pids
FROM
tblRuleList RuleList
WHERE
COALESCE(RuleList.bPMH, 0) = 1
AND
Rpt_Table = 'table2'
) AS a
UNION
ALL
SELECT
'Average Number of Quality Improvement Opportunities' AS MemberCharacteristic
, CONVERT(FLOAT,clinical_pids)/CONVERT(FLOAT,@v_iTotalPt_Cnt) AS Result
FROM
( SELECT
SUM(o_RuleNumerate) AS Clinical_pids
FROM
tblRuleList AS RuleList
WHERE
RuleList.bPMH = 0
AND
rpt_table = 'table3'
) AS a
UNION
ALL
SELECT
'% with > 1 Provider Visit over the pASt year' AS MemberCharacteristic
, (CONVERT(FLOAT,COUNT(*))/CONVERT(FLOAT,@v_iTotalPt_Cnt)*100) AS Result
FROM
( SELECT
RecType.PatientID
, COUNT(DISTINCT RecType.rhs_stdprov) AS nCnt_Prov
FROM
Mem_Elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
LEFT
JOIN
ReferenceDB.dbo.POS_Client_Codes PosC
ON
COALESCE(RecType.RHS_Serv, '') = PosC.pos_code_client
AND
PosC.Client_ID = @i_iClientID
LEFT
JOIN
ReferenceDB.dbo.POS_National_Codes PosN
ON
COALESCE(RecType.RHS_Serv, '') = PosN.pos_code_national
JOIN
vServiceCodes SC
ON
COALESCE(PosC.Pos_Code_National, PosN.Pos_Code_National) = SC.RHS_Serv
AND
SC.ServValue = 'L2_Outpt'
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
AND
RecType.StartDate
BETWEEN
DATEADD(DAY, -366, @i_dAAOD) AND DATEADD(DAY, -1, @i_dAAOD)
GROUP
BY
RecType.PatientID
HAVING
COUNT(DISTINCT RecType.RHS_StdProv) > 1
) AS a
UNION
ALL
SELECT
'Average Number of Provider Visits over the past year' AS MemberCharacteristic
, CONVERT(VARCHAR(10), ROUND(CONVERT(FLOAT,COUNT(*))
/ CONVERT(FLOAT,@v_iTotalPt_Cnt), 1)) AS Result
FROM
( SELECT
DISTINCT
RecType.PatientID
, RecType.RHS_stdprov
FROM
Mem_Elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
LEFT
JOIN
ReferenceDB.dbo.POS_Client_codes PosC
ON
COALESCE(RecType.RHS_Serv, '') = PosC.pos_code_client
AND
PosC.client_ID = @i_iClientID
LEFT
JOIN
ReferenceDB.dbo.POS_National_Codes PosN
ON
COALESCE(RecType.RHS_Serv, '') = PosN.POS_Code_National
JOIN
vServiceCodes SC
ON
COALESCE(PosC.pos_code_national, PosN.POS_Code_National) = SC.RHS_Serv
AND
SC.ServValue = 'L2_Outpt'
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
AND
RecType.StartDate
BETWEEN
DATEADD(DAY, -366, @i_dAAOD) AND DATEADD(DAY, -1, @i_dAAOD)

) AS a
UNION
ALL
SELECT
'Average Number of Rx Claims over the past year' AS MemberCharacteristic
, CONVERT(VARCHAR(10), ROUND(CONVERT(FLOAT,COUNT(*))
/ CONVERT(FLOAT,@v_iTotalPt_Cnt), 1)) AS Result
FROM
(SELECT
DISTINCT
RecType.SrcID
FROM
Mem_elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
AND
RecType.NDC IS NOT NULL
AND
(
RecType.StartDate >= DATEADD(dd, -366, @i_dAAOD)
and RecType.StartDate <= DATEADD(dd, -1, @i_dAAOD)
)
) A
UNION
ALL
SELECT
'Average Number of Medical Claims over the pASt year' as MemberCharacteristic
, CONVERT(VARCHAR(10), ROUND(CONVERT(FLOAT,COUNT(*))
/ CONVERT(FLOAT,@v_iTotalPt_Cnt), 1)) as Result
FROM
( SELECT
DISTINCT
RecType.SrcID
FROM
Mem_Elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
AND
RecType.NDC IS NULL
AND
RecType.StartDate
BETWEEN
DATEADD(DAY, -366, @i_dAAOD) AND DATEADD(DAY, -1, @i_dAAOD)
) AS a
UNION
ALL
SELECT
'Average Number of Non Hospital/ER Claims over the pASt year' AS MemberCharacteristic
, CONVERT(VARCHAR(10), ROUND(CONVERT(FLOAT,( ALLClaims.nCnt-ER_Hsp_Claims.nCnt))
/ CONVERT(FLOAT,@v_iTotalPt_Cnt), 1)) AS Result
FROM
( SELECT
COUNT(DISTINCT RecType.SrcID)AS nCnt
FROM
Mem_Elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
LEFT
JOIN
ReferenceDB.dbo.POS_Client_Codes PosC
ON
COALESCE(RecType.RHS_Serv, '') = PosC.pos_code_client
AND
PosC.client_ID = @i_iClientID
LEFT
JOIN
ReferenceDB.dbo.POS_National_Codes PosN
ON
COALESCE(RecType.RHS_Serv, '') = PosN.pos_code_national
JOIN
vServiceCodes SC
ON
COALESCE(PosC.POS_Code_National, PosN.POS_Code_National) = SC.RHS_Serv
AND
SC.ServValue IN ('L2_ED', 'L2_Inpt')
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
AND
RecType.NDC IS NULL
AND
(
RecType.StartDate >= DATEADD(DAY, -366, @i_dAAOD) AND
RecType.StartDate <= DATEADD(DAY, -1, @i_dAAOD)
)
) AS ER_Hsp_Claims

,
(SELECT
COUNT(DISTINCT RecType.SrcID)
FROM
Mem_Elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
WHERE
(
DATEADD(DAY, -1, @i_dAAOD) >= MemElg.EligStart AND
DATEADD(DAY, -1, @i_dAAOD) <= MemElg.EligEnd
)
AND
(
RecType.StartDate >= DATEADD(DAY, -366, @i_dAAOD) AND
RecType.StartDate <= DATEADD(DAY, -1, @i_dAAOD)
)
AND
RecType.NDC IS NULL
) AS member_char


/****************************End Member Characteristics ***************************/
--Output the Final Tables for reporting
SELECT
--DISTINCT
NULL AS OA_CCategory_L1
, NULL AS ruleCname
, NULL AS o_RuleNumerate
, NULL AS o_RuleDenominator
, NULL AS OA_US_Dis_Prevalence
, NULL AS OA_Perc_Conv
, NULL AS o_#_Mem_CONVERT
, NULL AS o_Expected_AnnSav_Per_Conversion
, NULL AS o_Gross_AnnSav
, NULL AS o_Pref_AvgCostSavingsPerMember
, NULL AS o_Pref_Conv_Rate
, NULL AS o_Pref_Cnt_Elig_Pts
, MemberCharacteristic
, Result
,'Table1' AS RPT_Table
FROM
Member_Characteristics

UNION
--Get Tables 2,3,5
SELECT
DISTINCT
OA_CCategory_L1
, ruleCname
, COALESCE(o_RuleNumerate,0) AS o_RuleNumerate
, COALESCE(o_RuleDenominator,0) AS o_RuleDenominator
, OA_US_Dis_Prevalence
, OA_Perc_Conv
, o_#_Mem_CONVERT
, o_Expected_AnnSav_Per_Conversion
, o_Gross_AnnSav
, NULL AS o_Pref_AvgCostSavingsPerMember
, NULL AS o_Pref_Conv_Rate
, NULL AS o_Pref_Cnt_Elig_Pts
, NULL AS MemberCharacteristic
, NULL as Result
, RPT_Table
FROM
tblRuleList
UNION
--Get a NULL row for table 4 which is a static table so that the grouping worksIN Crystal
SELECT
--DISTINCT
NULL AS OA_CCategory_L1
, NULL AS ruleCname
, NULL AS o_RuleNumerate
, NULL AS o_RuleDenominator
, NULL AS OA_US_Dis_Prevalence
, NULL AS OA_Perc_Conv
, NULL AS o_#_Mem_CONVERT
, NULL AS o_Expected_AnnSav_Per_Conversion
, NULL AS o_Gross_AnnSav
, NULL AS o_Pref_AvgCostSavingsPerMember
, NULL AS o_Pref_Conv_Rate
, NULL AS o_Pref_Cnt_Elig_Pts
, NULL AS MemberCharacteristic
, NULL result
, 'Table4' RPT_Table
FROM
tblRuleList
UNION

/***********process table 6: Cost Savings SUMmary FROM Table 5*************************/
SELECT
DISTINCT
'CardiovAScular Disease' AS OA_CCategory_L1
, 'CardiovAScular Disease' AS ruleCname
, SUM(COALESCE(o_RuleNumerate,0)) AS o_RuleNumerate
, SUM(COALESCE(o_RuleDenominator,0)) AS o_RuleDenominator
, NULL AS OA_US_Dis_Prevalence
, NULL AS OA_Perc_Conv
, SUM(COALESCE(o_#_Mem_CONVERT,0)) AS o_#_Mem_CONVERT
, SUM(COALESCE(o_Expected_AnnSav_Per_Conversion,0)) AS o_Expected_AnnSav_Per_Conversion
, (SUM(COALESCE(o_RuleNumerate,0)*COALESCE(o_Expected_AnnSav_Per_Conversion,0))) AS o_Gross_AnnSav
, NULL AS o_Pref_AvgCostSavingsPerMember
, NULL AS o_Pref_Conv_Rate
, NULL AS o_Pref_Cnt_Elig_Pts
, NULL AS MemberCharacteristic
, NULL AS result
, 'Table6' AS RPT_table
FROM
tblRuleList
WHERE
OA_CCategory_L1
LIKE
'CardiovAScular%'
AND
RPT_table = 'Table5'

UNION
SELECT
DISTINCT
OA_CCategory_L1 AS OA_CCategory_L1
, OA_CCategory_L1 AS ruleCname
, SUM(COALESCE(o_RuleNumerate,0)) AS o_RuleNumerate
, SUM(COALESCE(o_RuleDenominator,0)) AS o_RuleDenominator
, NULL AS OA_US_Dis_Prevalence
, NULL AS OA_Perc_Conv
, SUM(COALESCE(o_#_Mem_CONVERT,0)) AS o_#_Mem_CONVERT
, SUM(COALESCE(o_Expected_AnnSav_Per_Conversion,0)) AS o_Expected_AnnSav_Per_Conversion
, SUM(COALESCE(o_RuleNumerate,0)*COALESCE(o_Expected_AnnSav_Per_Conversion,0)) AS o_Gross_AnnSav
, NULL AS o_Pref_AvgCostSavingsPerMember
, NULL AS o_Pref_Conv_Rate
, NULL AS o_Pref_Cnt_Elig_Pts
, NULL AS MemberCharacteristic
, NULL as result
, 'Table6' AS RPT_table
FROM
tblRuleList
WHERE
OA_CCategory_L1 NOT LIKE 'Cardiovascular%'
AND
RPT_table = 'Table5'
GROUP
BY
OA_CCategory_L1

UNION
SELECT
NULL AS OA_CCategory_L1
, NULL AS ruleCname
, NULL AS o_RuleNumerate
, NULL AS o_RuleDenominator
, NULL AS OA_US_Dis_Prevalence
, NULL AS OA_Perc_Conv
, NULL AS o_#_Mem_CONVERT
, NULL AS o_Expected_AnnSav_Per_Conversion
, o_Gross_AnnSav = @v_Gross_AnnSav
, o_Pref_AvgCostSavingsPerMember = @v_Pref_AvgCostSavingsPerMember
, o_Pref_Conv_Rate = (CONVERT(VARCHAR(10), @i_fConv_Rate * 100) + '%')
, o_Pref_Cnt_Elig_Pts = @v_EligPtCnt
, NULL AS MemberCharacteristic
, NULL as result
, RPT_Table = 'Table7'
FROM
@v_tbl_TracerPt_AvgCost

--Table 8 createdIN Crystal

--exec _reports_OAMemberCharacteristics_Rule_Savings '20060929', 53, 0.16
--exec dbo._reports_OA_MemberDis_Prevalence_siji '20060928', 26
----------------------------------------------------------------------------------------------







 
Sort by date Sort by votes
Can you post just the problem section of the procedure?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog
 
Upvote 0 Downvote
And what you have on line 1129?

BTW remove all @CRLF from you dynamically built queries. There is no need of it and also makes the code more unreadable.
That is perfectly valid:
Code: 
SET @SQL = 'INSERT INTO TblRuleList (RuleCName
                                   , RuleNumber
                                   , Client_ID 
                                   , rcID
                                   , bPMH
                                   ,Rpt_Table)
            SELECT   RefOA.OA_RuleCName
                   , rc.RuleNumber
                   , rc.Client_ID
                   , rc.RcID
                   , COALESCE(bPMH, 0)
                   , Rpt_Table = CASE COALESCE(bPMH, 0)
                                 WHEN 1
                   THEN ''Table2''
                  'ELSE ''Table3'' END
            FROM vRuleCandidates rc
                 JOIN '+ @ce_Database_Name+'.dbo.ref_OA_RuleAttributes RefOA
                      ON rc.rcID = refOA.rcID
            WHERE refOA.OA_FORMULARY_FLAG = ''N'''
Also there is no need to have @QUOTE variable. Just double quotes which are inside the string.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
Upvote 0 Downvote
First - I didnt write it this way - it was a gift (grin).

Second, the problem is within this area:

,
(SELECT
COUNT(DISTINCT RecType.SrcID)
FROM
Mem_Elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
WHERE
(
DATEADD(DAY, -1, @i_dAAOD) >= MemElg.EligStart AND
DATEADD(DAY, -1, @i_dAAOD) <= MemElg.EligEnd
)
AND
(
RecType.StartDate >= DATEADD(DAY, -366, @i_dAAOD) AND
RecType.StartDate <= DATEADD(DAY, -1, @i_dAAOD)
)
AND
RecType.NDC IS NULL
) AS member_char


/****************************End Member Characteristics ***************************/
--Output the Final Tables for reporting
SELECT
--DISTINCT
NULL AS OA_CCategory_L1
, NULL AS ruleCname
, NULL AS o_RuleNumerate
, NULL AS o_RuleDenominator
, NULL AS OA_US_Dis_Prevalence
, NULL AS OA_Perc_Conv
, NULL AS o_#_Mem_CONVERT
, NULL AS o_Expected_AnnSav_Per_Conversion
, NULL AS o_Gross_AnnSav
, NULL AS o_Pref_AvgCostSavingsPerMember
, NULL AS o_Pref_Conv_Rate
, NULL AS o_Pref_Cnt_Elig_Pts
, MemberCharacteristic
, Result
,'Table1' AS RPT_Table
FROM
Member_Characteristics
 
Upvote 0 Downvote
When you double click on the error message and it mark the code could you post the few lines above and few lines below the marked code?

BTW it is up to you to change that code now ;-)


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
Upvote 0 Downvote
It highlights the SELECT in the code below - I grabbed just a bit above and below. I just dont see it.....

and yes, I will be re-writing it!! first I need to get data...




WHERE
(
DATEADD(DAY, -1, @i_dAAOD) >= MemElg.EligStart AND
DATEADD(DAY, -1, @i_dAAOD) <= MemElg.EligEnd
)
AND
(
RecType.StartDate >= DATEADD(DAY, -366, @i_dAAOD) AND
RecType.StartDate <= DATEADD(DAY, -1, @i_dAAOD)
)
AND
RecType.NDC IS NULL
) AS member_char


/****************************End Member Characteristics ***************************/
--Output the Final Tables for reporting
SELECT
--DISTINCT
NULL AS OA_CCategory_L1
, NULL AS ruleCname
, NULL AS o_RuleNumerate
, NULL AS o_RuleDenominator
, NULL AS OA_US_Dis_Prevalence
, NULL AS OA_Perc_Conv
, NULL AS o_#_Mem_CONVERT
, NULL AS o_Expected_AnnSav_Per_Conversion
, NULL AS o_Gross_AnnSav
, NULL AS o_Pref_AvgCostSavingsPerMember
, NULL AS o_Pref_Conv_Rate
, NULL AS o_Pref_Cnt_Elig_Pts
, MemberCharacteristic
, Result
,'Table1' AS RPT_Table
FROM
Member_Characteristics
 
Upvote 0 Downvote
Can you post the statement above that select as well?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog
 
Upvote 0 Downvote
Sure thing.
Above that select statement is this:

/****************************Start Member Characteristics ***************************/
SELECT
DISTINCT
MemberCharacteristic
, Result
INTO
Member_Characteristics
FROM
(SELECT
'Total Number of Eligible Members' AS MemberCharacteristic
, @v_iTotalPt_Cnt AS Result
UNION
ALL
SELECT
'Male(%)' AS MemberCharacteristic
, ROUND(CONVERT(FLOAT,COUNT(DISTINCT MemElg.PatientID))
/ CONVERT(FLOAT,@v_iTotalPt_Cnt)*100, 1) AS Result
FROM
Mem_Elg MemElg
JOIN
FL_MemInfo MemInfo
ON
MemElg.PatientID = MemInfo.PatientID
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
AND
Sex = 'M'
UNION
ALL
SELECT
'Average Age' AS MemberCharacteristic
, AVG(DATEDIFF(YEAR, Birthdate, @i_dAAOD)) AS Result
FROM (
SELECT
DISTINCT
MemInfo.patientID
, MemInfo.Birthdate
FROM
Mem_Elg MemElg
JOIN
FL_MemInfo MemInfo
ON
MemElg.PatientID = MemInfo.PatientID
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
) AS a
UNION
ALL
SELECT
'Average Number of Significant Co-morbidities *' AS MemberCharacteristic
, CONVERT(FLOAT,pmh_pids)/CONVERT(FLOAT,@v_iTotalPt_Cnt) AS Result
FROM
( SELECT
DISTINCT
SUM(o_RuleNumerate) AS Pmh_pids
FROM
tblRuleList RuleList
WHERE
COALESCE(RuleList.bPMH, 0) = 1
AND
Rpt_Table = 'table2'
) AS a
UNION
ALL
SELECT
'Average Number of Quality Improvement Opportunities' AS MemberCharacteristic
, CONVERT(FLOAT,clinical_pids)/CONVERT(FLOAT,@v_iTotalPt_Cnt) AS Result
FROM
( SELECT
SUM(o_RuleNumerate) AS Clinical_pids
FROM
tblRuleList AS RuleList
WHERE
RuleList.bPMH = 0
AND
rpt_table = 'table3'
) AS a
UNION
ALL
SELECT
'% with > 1 Provider Visit over the pASt year' AS MemberCharacteristic
, (CONVERT(FLOAT,COUNT(*))/CONVERT(FLOAT,@v_iTotalPt_Cnt)*100) AS Result
FROM
( SELECT
RecType.PatientID
, COUNT(DISTINCT RecType.rhs_stdprov) AS nCnt_Prov
FROM
Mem_Elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
LEFT
JOIN
ReferenceDB.dbo.POS_Client_Codes PosC
ON
COALESCE(RecType.RHS_Serv, '') = PosC.pos_code_client
AND
PosC.Client_ID = @i_iClientID
LEFT
JOIN
ReferenceDB.dbo.POS_National_Codes PosN
ON
COALESCE(RecType.RHS_Serv, '') = PosN.pos_code_national
JOIN
vServiceCodes SC
ON
COALESCE(PosC.Pos_Code_National, PosN.Pos_Code_National) = SC.RHS_Serv
AND
SC.ServValue = 'L2_Outpt'
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
AND
RecType.StartDate
BETWEEN
DATEADD(DAY, -366, @i_dAAOD) AND DATEADD(DAY, -1, @i_dAAOD)
GROUP
BY
RecType.PatientID
HAVING
COUNT(DISTINCT RecType.RHS_StdProv) > 1
) AS a
UNION
ALL
SELECT
'Average Number of Provider Visits over the past year' AS MemberCharacteristic
, CONVERT(VARCHAR(10), ROUND(CONVERT(FLOAT,COUNT(*))
/ CONVERT(FLOAT,@v_iTotalPt_Cnt), 1)) AS Result
FROM
( SELECT
DISTINCT
RecType.PatientID
, RecType.RHS_stdprov
FROM
Mem_Elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
LEFT
JOIN
ReferenceDB.dbo.POS_Client_codes PosC
ON
COALESCE(RecType.RHS_Serv, '') = PosC.pos_code_client
AND
PosC.client_ID = @i_iClientID
LEFT
JOIN
ReferenceDB.dbo.POS_National_Codes PosN
ON
COALESCE(RecType.RHS_Serv, '') = PosN.POS_Code_National
JOIN
vServiceCodes SC
ON
COALESCE(PosC.pos_code_national, PosN.POS_Code_National) = SC.RHS_Serv
AND
SC.ServValue = 'L2_Outpt'
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
AND
RecType.StartDate
BETWEEN
DATEADD(DAY, -366, @i_dAAOD) AND DATEADD(DAY, -1, @i_dAAOD)

) AS a
UNION
ALL
SELECT
'Average Number of Rx Claims over the past year' AS MemberCharacteristic
, CONVERT(VARCHAR(10), ROUND(CONVERT(FLOAT,COUNT(*))
/ CONVERT(FLOAT,@v_iTotalPt_Cnt), 1)) AS Result
FROM
(SELECT
DISTINCT
RecType.SrcID
FROM
Mem_elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
AND
RecType.NDC IS NOT NULL
AND
(
RecType.StartDate >= DATEADD(dd, -366, @i_dAAOD)
and RecType.StartDate <= DATEADD(dd, -1, @i_dAAOD)
)
) A
UNION
ALL
SELECT
'Average Number of Medical Claims over the pASt year' as MemberCharacteristic
, CONVERT(VARCHAR(10), ROUND(CONVERT(FLOAT,COUNT(*))
/ CONVERT(FLOAT,@v_iTotalPt_Cnt), 1)) as Result
FROM
( SELECT
DISTINCT
RecType.SrcID
FROM
Mem_Elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
AND
RecType.NDC IS NULL
AND
RecType.StartDate
BETWEEN
DATEADD(DAY, -366, @i_dAAOD) AND DATEADD(DAY, -1, @i_dAAOD)
) AS a
UNION
ALL
SELECT
'Average Number of Non Hospital/ER Claims over the pASt year' AS MemberCharacteristic
, CONVERT(VARCHAR(10), ROUND(CONVERT(FLOAT,( ALLClaims.nCnt-ER_Hsp_Claims.nCnt))
/ CONVERT(FLOAT,@v_iTotalPt_Cnt), 1)) AS Result
FROM
( SELECT
COUNT(DISTINCT RecType.SrcID)AS nCnt
FROM
Mem_Elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
LEFT
JOIN
ReferenceDB.dbo.POS_Client_Codes PosC
ON
COALESCE(RecType.RHS_Serv, '') = PosC.pos_code_client
AND
PosC.client_ID = @i_iClientID
LEFT
JOIN
ReferenceDB.dbo.POS_National_Codes PosN
ON
COALESCE(RecType.RHS_Serv, '') = PosN.pos_code_national
JOIN
vServiceCodes SC
ON
COALESCE(PosC.POS_Code_National, PosN.POS_Code_National) = SC.RHS_Serv
AND
SC.ServValue IN ('L2_ED', 'L2_Inpt')
WHERE
DATEADD(DAY, -1, @i_dAAOD)
BETWEEN
MemElg.EligStart AND MemElg.EligEnd
AND
RecType.NDC IS NULL
AND
(
RecType.StartDate >= DATEADD(DAY, -366, @i_dAAOD) AND
RecType.StartDate <= DATEADD(DAY, -1, @i_dAAOD)
)
) AS ER_Hsp_Claims

,
(SELECT
COUNT(DISTINCT RecType.SrcID)
FROM
Mem_Elg MemElg
JOIN
FL_RecType RecType
ON
MemElg.PatientID = RecType.PatientID
WHERE
(
DATEADD(DAY, -1, @i_dAAOD) >= MemElg.EligStart AND
DATEADD(DAY, -1, @i_dAAOD) <= MemElg.EligEnd
)
AND
(
RecType.StartDate >= DATEADD(DAY, -366, @i_dAAOD) AND
RecType.StartDate <= DATEADD(DAY, -1, @i_dAAOD)
)
AND
RecType.NDC IS NULL
) AS member_char

 
Upvote 0 Downvote
My god what a disaster.

May I suggest that just for starters you remove extraneous line breaks and parentheses? The person who wrote all this obviously had his own consistent "style" but I don't agree that the highest virtue is consistency. The content of the style matters too. So fix all those line breaks by putting things on one line where possible:

Code: 
[red]               AND 
                    RecType.NDC IS NULL[/red]
Code: 
               AND RecType.NDC IS NULL
Code: 
[red]                  FROM 
                        Mem_Elg MemElg
                  JOIN 
                        FL_RecType RecType 
                    ON 
                        MemElg.PatientID = RecType.PatientID[/red]
Code: 
               FROM
                        Mem_Elg MemElg
                        JOIN FL_RecType RecType ON MemElg.PatientID = RecType.PatientID
In a long query that is hard to understand, there is even warrant for more collapsing:
Code: 
               FROM Mem_Elg MemElg JOIN FL_RecType RecType ON MemElg.PatientID = RecType.PatientID
It's not really fair to us to post hundreds of lines of code that is mostly empty space. We can't scan it very well.
 
Upvote 0 Downvote
start commenting out sections of the code and then trying again, if it still doesn't work, try a new section...

do this until it works, and the last section you commented out is the culprit, so you can do the same process but with more detail...

it's probably a bracket or comma somewhere...

--------------------
Procrastinate Now!
 
Upvote 0 Downvote
Try change
Code: 
        ) AS ER_Hsp_Claims
        
        ,
                (SELECT
to
Code: 
        ) AS ER_Hsp_Claims
        
        union all
                (SELECT
and
Code: 
                        RecType.NDC IS NULL
            ) AS member_char
to
Code: 
                        RecType.NDC IS NULL
            ) as a    
          ) AS member_char
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
375
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
281
Andrzejek
Andrzejek
ahmedsa2018
  • Locked
  • Question
the goal mfrom asking question is t
Replies
0
Views
93
ahmedsa2018
ahmedsa2018
Virendra Yaduvanshi
  • Locked
  • Question
SSRS - Subscription page error
Replies
0
Views
107
Virendra Yaduvanshi
Virendra Yaduvanshi
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
145
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top