January 18, 2011 at 7:43 am
Its always easier when it is something you wrote, plus I am new to tsql.
Here is what he wants ...
------------------------
Adam,
There is a report in reporting services that Lisa uses. Can you check on this? I think the some markets may be hard coded in the SQL statement that is pulling this data. Make sure the SQL statement is pulling live data—if it’s pulling from a compiled table, make sure that table is being refreshed nightly.
Let me know if you have questions.
Thanks.
Richard
---------------
the markets that are hard coded are this line I presume,:
and MPI.GroupName IN ('ELP','NMX','VLY'),
but how to make them none hard coded?
--------------
thanks so MUCH IN ADVANCE!
Adam
SET NOCOUNT ON
--GET MATRIX CODES FOUND
DECLARE @rptMatrixMetrics AS TABLE (Market varchar(3), GMPI int, MatrixFound int, PHCprevKnew int, CountValidated int, CountFailed int, DataRapOtherCodes int,
RAFScoreValidated decimal(5,3), RAFScoreDataRap decimal(5,3))
SELECT
AE_AuditDate,
DateOfService = CASE WHEN DAD.DM_ConfirmationID = 2 --Agreed
THEN isnull(DAE.AE_PatientEncounterDate, DAD.AD_Date)
WHEN DAD.DM_ConfirmationID = 5 --Confirmed
THEN DAD.AD_Date
ELSE DAD.AD_Date
END,
ICD9 = DAD.AD_ICD9,
Market = MPI.GroupName,
DAE.GMPI,
DAD.AD_Action,
DAD.DM_ConfirmationID,
Factor = CONVERT(decimal(5,3),NULL),
HCC = CONVERT(int, NULL)
INTO #MatrixFound
FROM
DataRap.dbo.tblDM_AuditEncounter DAE
JOIN
DataRap.dbo.tblDM_AuditDiag DAD ON DAE.DM_AuditEncounterID = DAD.DM_AuditEncounterID
JOIN
MasterPatientIndex..tblMPICurrentData MPI ON DAE.GMPI = MPI.GMPI
WHERE
PatientVisitTypeID = 9 -- Matrix
and MPI.GroupName IN ('ELP','NMX','VLY')
and
(
(DAD.AD_Action = 'No Plan' AND DAD.DM_ConfirmationID = 1)
OR
(DAD.AD_Action = 'Add' AND DAD.DM_ConfirmationID = 5)
)
order by DAE.GMPI
--GET RAF SCORES
--FIRST UPDATE THE SCORE FOR THE ICD9 CODES
UPDATE a
SET a.Factor = r.Factor, a.HCC = i.HCC
FROM #MatrixFound a
JOIN DataRapRAF.dbo.tblICDToHCCLookup i ON a.ICD9 = i.ICD9
JOIN DataRapRAF.dbo.tblHCCRateLookup r ON i.HCC = r.HCC AND r.FactorType = 'Comm' AND PaymentDateEnd IS NULL
INSERT @rptMatrixMetrics (Market, GMPI, MatrixFound)
SELECT Market, GMPI, Count(*) FROM #MatrixFound GROUP BY Market, GMPI
--FIND CODES THAT WE PREVIOUSLY KNEW ABOUT
SELECT a.GMPI, a.ICD9
INTO #KnownCodesICD9
FROM ztblAggICD9 a JOIN #MatrixFound b ON a.GMPI = b.GMPI AND a.ICD9 = b.ICD9
WHERE a.DateOfService > '1/1/' + CONVERT(CHAR(4),YEAR(DATEADD(yy,-1,GETDATE())))
AND a.GMPI IN (SELECT GMPI FROM #MatrixFound)
AND a.SourceTypeID = 2
--AND a.OKtoSubmit = 1
GROUP BY a.GMPI, a.ICD9
DECLARE @KnownCodes AS TABLE(GMPI int, RecCount int)
INSERT @KnownCodes(GMPI, RecCount)
SELECT a.GMPI, RecCount=count(*)
FROM #KnownCodesICD9 a
GROUP BY a.GMPI
UPDATE a
SET a.PHCprevKnew = b.RecCount
FROM @rptMatrixMetrics a JOIN @KnownCodes b
ON a.GMPI = b.GMPI
--FIND CODES THAT MATRIX DID NOT KNOW ABOUT
--drop table #MatrixNotKnownCodesICD9; drop table #MatrixNotKnownCodesHCC
SELECT a.GMPI, a.ICD9, a.HCC, Factor = CONVERT(decimal(5,3),NULL)
INTO #MatrixNotKnownCodesICD9
FROM ztblAggICD9 a LEFT JOIN #MatrixFound b ON a.GMPI = b.GMPI AND a.ICD9 = b.ICD9
WHERE a.DateOfService > '1/1/' + CONVERT(CHAR(4),YEAR(DATEADD(yy,-1,GETDATE())))
AND a.GMPI IN (SELECT GMPI FROM #MatrixFound)
AND a.SourceTypeID = 2
AND a.OKtoSubmit = 1
AND b.ICD9 IS NULL
AND a.HCC IS NOT NULL
GROUP BY a.GMPI, a.ICD9, a.HCC
DECLARE @UnKnownCodes AS TABLE(GMPI int, RecCount int)
INSERT @UnKnownCodes(GMPI, RecCount)
SELECT a.GMPI, RecCount=count(*)
FROM #MatrixNotKnownCodesICD9 a
GROUP BY a.GMPI
UPDATE a
SET a.DataRapOtherCodes = b.RecCount
FROM @rptMatrixMetrics a JOIN @UnKnownCodes b
ON a.GMPI = b.GMPI
UPDATE a
SET a.Factor = r.Factor
FROM #MatrixNotKnownCodesICD9 a
JOIN DataRapRAF.dbo.tblHCCRateLookup r ON a.HCC = r.HCC AND r.FactorType = 'Comm' AND PaymentDateEnd IS NULL
;with MatrixNotKnownCodesHCC as (
SELECT GMPI, HCC, Factor
FROM #MatrixNotKnownCodesICD9
GROUP BY GMPI, HCC, Factor
)
SELECT GMPI, SumFactor = SUM(Factor)
INTO #MatrixNotKnownCodesHCC
FROM MatrixNotKnownCodesHCC
GROUP BY GMPI
UPDATE a
SET a.RAFScoreDataRap = SumFactor
FROM @rptMatrixMetrics a JOIN #MatrixNotKnownCodesHCC b
ON a.GMPI = b.GMPI
--OF CODES THAT WE DIDN'T KNOW ABOUT, FIND STATUS OF DOCUMENTATION (PLAN OR NO PLAN)
SELECT GMPI, ICD9, HCC, DocStatus = CASE WHEN AD_Action = 'No Plan' THEN 'No Plan' ELSE 'With Plan' END, Factor
INTO #PlansICD9
FROM #MatrixFound
--select * from #PlansICD9 order by gmpi
--drop table #PlansHCC
SELECT GMPI, DocStatus, RecCount= COUNT(*), SumFactor = Sum(Factor), HCCCount= COUNT(DISTINCT HCC)
INTO #Plans
FROM #PlansICD9
GROUP BY GMPI, DocStatus
;with PlansHCC as (
SELECT GMPI, DocStatus, HCC, Factor
--INTO #PlansHCC
FROM #PlansICD9
GROUP BY GMPI, HCC, DocStatus, Factor
)
SELECT GMPI, DocStatus, SumFactor = SUM(Factor)
INTO #PlansHCC
FROM PlansHCC
GROUP BY GMPI, DocStatus
UPDATE a
SET a.CountValidated = b.RecCount
FROM @rptMatrixMetrics a JOIN #Plans b
ON a.GMPI = b.GMPI AND DocStatus = 'With Plan'
UPDATE a
SET a.RAFScoreValidated = SumFactor
FROM @rptMatrixMetrics a JOIN #PlansHCC b
ON a.GMPI = b.GMPI AND DocStatus = 'With Plan'
UPDATE a
SET a.CountFailed = b.RecCount
FROM @rptMatrixMetrics a JOIN #Plans b
ON a.GMPI = b.GMPI AND DocStatus = 'No Plan'
SELECT * FROM @rptMatrixMetrics
drop table #MatrixFound
drop table #PlansICD9
drop table #KnownCodesICD9
drop table #Plans
drop table #PlansHCC
drop table #MatrixNotKnownCodesICD9;
drop table #MatrixNotKnownCodesHCC
January 18, 2011 at 7:49 am
I would create a table called GroupName_codes or something and then put these values in there and restrict your qeury by using an inner join to this table, this way the codes can be modified by adding or removing them from the table rather than changing the sql query code.
Also since you are using report services you could put these codes from the lookup table into a drop-down parameter filter.
January 18, 2011 at 8:53 am
Unfortunately, I am experienced with SSIS and front end apps, so even what you are talking about in the lookup table and report filter I would have to learn how to do.
My thoughts would be to make it a stored proc and pass in the hard coded but this is Reporting,
Like take the hard code values from the variables out and make it a stored proc with 3 parms. But this is a different animal SSRS, hmmm.
USE AdamTestDB
DECLARE @GroupName1 char(3),@GroupName2 char(3),@GroupName3 char(3)
--these need to be in the report select from a lookup table in the SSRS
set @GroupName1 = 'ELP'
set @GroupName2 = 'NMX'
set @GroupName3 = 'VLY'
select * from dbo.TblTestNonHardCodedMarkets
where GroupName_IE_Markets IN (@GroupName1, @GroupName2, @GroupName3)
January 18, 2011 at 9:18 am
even if you went with stored procedures you will still have to 'hard-code' the values somewhere. to me the opposite of hard-coding is to put the values in somewhere where they can be changed without changing any code, such as a table.
If you are struggling with my approach I can break down the steps it should be fairly straight foward esp. if you have experience in SSIS
January 18, 2011 at 9:29 am
OK so I added this:
SELECT Distinct GroupName
INTO #GroupNames
FROM MasterPatientIndex..tblMPICurrentData
WHERE GroupName IS NOT NULL
atthe end of course I dropped it.
Not exactly sure where I inner join Temp table?
January 18, 2011 at 9:59 am
I would add the join here, instead of the where clause
FROM
DataRap.dbo.tblDM_AuditEncounter DAE
JOIN
DataRap.dbo.tblDM_AuditDiag DAD ON DAE.DM_AuditEncounterID = DAD.DM_AuditEncounterID
JOIN
MasterPatientIndex..tblMPICurrentData MPI ON DAE.GMPI = MPI.GMPI
WHERE
PatientVisitTypeID = 9 -- Matrix
and MPI.GroupName IN ('ELP','NMX','VLY')
FROM
DataRap.dbo.tblDM_AuditEncounter DAE
JOIN
DataRap.dbo.tblDM_AuditDiag DAD ON DAE.DM_AuditEncounterID = DAD.DM_AuditEncounterID
JOIN
MasterPatientIndex..tblMPICurrentData MPI ON DAE.GMPI = MPI.GMPI
Inner Join
#GroupNames on #GroupNames.groupname= MPI.GroupName
WHERE
PatientVisitTypeID = 9 -- Matrix
I would consider creating a perm table rather than populating a temp table as that seems to defeat the purpose of the where clause since you are simply adding all the codes from the table
January 19, 2011 at 11:53 am
Super Thanks. I am working on what you perscribed!
January 25, 2011 at 10:51 am
steveb. (1/18/2011)
even if you went with stored procedures you will still have to 'hard-code' the values somewhere. to me the opposite of hard-coding is to put the values in somewhere where they can be changed without changing any code, such as a table.
Or a variable, isn't it?
What "boss" wants is to have all queries using bind-variables so to avoid hard parsing.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply