February 16, 2011 at 10:40 am
Here is the part I do not understand. Below is the whole script.
--DELETE DUPLICATE CLUSTERS
DELETE FROM
t1
--select t1.*
FROM
#PRESubmissions t1
INNER JOIN
(
SELECT
MAX(RowCounter) AS RowCounter,
MemberID,
DOS,
ICD9,
IngenixPOS
FROM
#PRESubmissions
GROUP BY
MemberID,
DOS,
ICD9,
IngenixPOS
HAVING
COUNT(*) > 1
) t2
ON(
t1.MemberID = t2.MemberID
AND t1.DOS = t2.DOS
AND t1.ICD9 = t2.ICD9
AND t1.IngenixPOS = t2.IngenixPOS
AND t1.RowCounter <> t2.RowCounter
)
USE [DataRapRaf]
GO
/****** Object: StoredProcedure [dbo].[spCreateSubmissionFileIngenix] Script Date: 02/16/2011 11:33:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCreateSubmissionFileIngenix]
@SubmissionTimelineID int,
@SubmissionBatchID int
AS
--DECLARE @SubmissionTimelineID int;SET @SubmissionTimelineID = 14
DECLARE @DOSStart char(6), @DOSEnd char(6), @ThisServer varchar(25)
SELECT @DOSStart = DOSStart, @DOSEnd = DOSEnd FROM tblSubmissionTimelines
WHERE SubmissionTimelineID = @SubmissionTimelineID
--CREATE TEMP SUBMISSIONS TABLE
--DROP TABLE #PRESubmissions; DROP TABLE #Submissions;DROP TABLE #tblMemberMonthsEligibilityCapitated
-- TRUNCATE TABLE #PRESubmissions
CREATE TABLE #PRESubmissions
(
SubmissionID int NULL,
MemberID char(9) NULL,
GMPI int NULL,
ICD9 char(6) NULL,
IngenixPOS char(1) NULL,
DOS char(8) NULL,
DOSDateTime datetime,
CPT char(5) NULL,
HICNumber varchar(50) NULL,
Market varchar(3) NULL,
POSAggICD9 varchar(10) NULL,
SubmissionBatchID int NULL,
RowCounter int IDENTITY(1,1) NOT NULL,
BirthDate char(8),
FileNum varchar(2)
)
INSERT #PRESubmissions (GMPI, ICD9, DOS, DOSDateTime, Market, POSAggICD9, SubmissionBatchID)
SELECT GMPI, ICD9, CONVERT(char(8),DateOfService,1), DateOfService, Market, POS, @SubmissionBatchID
FROM ztblAggICD9
WHERE CONVERT(char(6),DateOfService,112) BETWEEN @DOSStart AND @DOSEnd
AND OkToSubmit = 1
AND Market IN ('SAT','NEF','DNG','GNZ','DFW')
AND LEN(ICD9) <= 6
GROUP BY GMPI, ICD9, CONVERT(char(8),DateOfService,1), DateOfService, Market, POS, SourceTypeID
UPDATE a
SET a.IngenixPOS = b.IngenixBillType
--SELECT *
FROM #PRESubmissions a JOIN tblPlaceOfServiceLookup b
ON a.POSAggICD9 = b.PLACE
UPDATE a
SET a.IngenixPOS = 'P'
--SELECT *
FROM #PRESubmissions a
WHERE IngenixPOS IS NULL
/************* start here*********/
--DROP TABLE #tblMemberMonthsEligibilityCapitated
SELECT e.ID_OTHER_NoDash, e.GMPI, Market = pm_loc_cd, YearMonth
INTO #tblMemberMonthsEligibilityCapitated
FROM tblMemberMonthsEligibilityCapitated e
WHERE YearMonth BETWEEN @DOSStart AND @DOSEnd
AND pm_loc_cd NOT IN ('EHP','ARC')
AND Insco = 'SH'
CREATE CLUSTERED INDEX IX_MME_GMPI ON #tblMemberMonthsEligibilityCapitated(GMPI)
UPDATE a
SET a.MemberID = RIGHT(b.ID_OTHER_NoDash,9), a.HICNumber = RIGHT(b.ID_OTHER_NoDash,9)
--select a.GMPI, CONVERT(char(6),a.DOSDateTime,112)
FROM #PRESubmissions a JOIN #tblMemberMonthsEligibilityCapitated b
ON a.GMPI = b.GMPI AND CONVERT(char(6),a.DOSDateTime,112) = b.YearMonth
--Remove DOD members
Delete #PRESubmissions
where MemberID like '%DOD%'
--DELETE ANY ROWS WHERE THE MEMBER IS NOT ELIGBILE DURING THE DOS
DELETE a
--SELECT *
FROM #PRESubmissions a
WHERE NOT EXISTS (SELECT GMPI FROM #tblMemberMonthsEligibilityCapitated b
WHERE a.GMPI = b.GMPI AND CONVERT(char(6),a.DOSDateTime,112) = YearMonth)
--select count(*) from #PRESubmissions where MemberID is null
SELECT * INTO #ztblAggCPT FROM ReportSvs..ztblAggCPT
DELETE #ztblAggCPT
WHERE CONVERT(char(6),DateOfService,112) NOT BETWEEN @DOSStart AND @DOSEnd
CREATE CLUSTERED INDEX IX_ztblAggCPT_GMPI ON #ztblAggCPT(GMPI)
--UPDATE CPT VALUES
--FIND MAX CPT CODE BUT EXCLUDE HCPCS (HIX-PIX)
;WITH M AS
(
SELECT MemberID, DOS, ICD9, IngenixPOS, CPT = MAX(b.CPT)
FROM #PRESubmissions a JOIN #ztblAggCPT b
ON a.GMPI = b.GMPI AND a.DOSDateTime = b.DateOfService
WHERE b.CPT <= '99999'
GROUP BY MemberID, DOS, ICD9, IngenixPOS
)
UPDATE a
SET a.CPT = b.CPT
FROM #PRESubmissions a JOIN M b
ON a.MemberID = b.MemberID AND a.DOS = b.DOS AND a.ICD9 = b.ICD9 AND a.IngenixPOS = b.IngenixPOS
--FIND MAX CPT CODE OVERALL WHERE STILL NULL
;WITH M AS
(
SELECT MemberID, DOS, ICD9, IngenixPOS, CPT = MAX(b.CPT)
FROM #PRESubmissions a JOIN #ztblAggCPT b
ON a.GMPI = b.GMPI AND a.DOSDateTime = b.DateOfService
WHERE a.CPT IS NULL
GROUP BY MemberID, DOS, ICD9, IngenixPOS
)
UPDATE a
SET a.CPT = b.CPT
FROM #PRESubmissions a JOIN M b
ON a.MemberID = b.MemberID AND a.DOS = b.DOS AND a.ICD9 = b.ICD9 AND a.IngenixPOS = b.IngenixPOS
--COVERT ANY CUSTOM CPTS USED BY WELLMED TO REAL CPTS
UPDATE a
SET a.CPT = b.NewCPT
FROM #PRESubmissions a JOIN ReportSvs..tblCPTConversions b
ON a.CPT = b.OldCPT
--CONVERT 3 DIGIT CODES AND ANYTHING WITH 'DR' INTO A HOSPITAL CPT: 99233
UPDATE a
SET a.CPT = '99233'
--SELECT *
FROM #PRESubmissions a
WHERE LEN(CPT) = 3
UPDATE a
SET a.CPT = '99233'
--SELECT *
FROM #PRESubmissions a
WHERE CPT LIKE '%DR%'
--UPDATE THE REST WITH 99214
UPDATE #PRESubmissions
SET CPT = '99214'
WHERE CPT IS NULL
--DELETE DUPLICATE CLUSTERS
DELETE FROM
t1
--select t1.*
FROM
#PRESubmissions t1
INNER JOIN
(
SELECT
MAX(RowCounter) AS RowCounter,
MemberID,
DOS,
ICD9,
IngenixPOS
FROM
#PRESubmissions
GROUP BY
MemberID,
DOS,
ICD9,
IngenixPOS
HAVING
COUNT(*) > 1
) t2
ON(
t1.MemberID = t2.MemberID
AND t1.DOS = t2.DOS
AND t1.ICD9 = t2.ICD9
AND t1.IngenixPOS = t2.IngenixPOS
AND t1.RowCounter <> t2.RowCounter
)
--DELETE INVALID ICD9 CODES -- THESE WILL JUST BE REJECTED BY CMS
CREATE CLUSTERED INDEX ix_ICD9 ON #PRESubmissions(ICD9)
DELETE a
--SELECT count(*)
FROM #PRESubmissions a
WHERE NOT EXISTS (SELECT ICD9 FROM DataRap.dbo.tblDM_ICD9Lookup b
WHERE a.ICD9 = b.ICD9 AND ICD9CMSName = 1)
DROP INDEX ix_ICD9 ON #PRESubmissions
CREATE CLUSTERED INDEX IX_PRESubmissions_GMPI ON #PRESubmissions(MemberID)
--DROP TABLE #PRESubmissionsGroupByCluster
SELECT
ZeroBasedCount = Row_Number()
OVER (Partition By MemberID, DOS, IngenixPOS ORDER BY MemberID, DOS, IngenixPOS ASC)-1,
DiagRow = ((Row_Number()
OVER (Partition By MemberID, DOS, IngenixPOS ORDER BY MemberID, DOS, IngenixPOS ASC))-1)/ 9,
DiagCol = 'D' + RIGHT(((Row_Number()
OVER (Partition By MemberID, DOS, IngenixPOS ORDER BY MemberID, DOS, IngenixPOS ASC))-1)%9,1),
MemberID, DOS, ICD9, IngenixPOS, CPT
INTO #PRESubmissionsGroupByCluster
FROM #PRESubmissions
--DROP TABLE #Submissions
SELECT MemberID, BillType, FromDOS, ToDOS, D0=MIN(D0), D1=MIN(D1), D2=MIN(D2), D3=MIN(D3), D4=MIN(D4), D5=MIN(D5), D6=MIN(D6), D7=MIN(D7), D8=MIN(D8), CPT
, [FileNum] = dbo.padString(((Row_Number() OVER (ORDER BY MemberID ASC)-1)/20000)+1,2,'0',0)
INTO #Submissions
FROM
(
SELECT DiagRow, MemberID, BillType = IngenixPOS, FromDOS = DOS, ToDOS = DOS, CPT, [D0],[D1],[D2],[D3],[D4],[D5],[D6],[D7],[D8]
FROM #PRESubmissionsGroupByCluster
PIVOT (MIN(ICD9) FOR DiagCol IN ([D0],[D1],[D2],[D3],[D4],[D5],[D6],[D7],[D8])) AS p
) a
GROUP BY MemberID, BillType, FromDOS, ToDOS, CPT, DiagRow
ORDER BY MemberID, BillType, FromDOS
CREATE CLUSTERED INDEX IX_Submissions_MemberID ON #Submissions(MemberID)
--INSERT INTO tblSubmissions AND RETRIEVE IDENTITY COLUMN
INSERT tblSubmissions (GMPI, ICD9, ProviderType, DOS, HICNumber, Market, CurrentStatus)
SELECT GMPI, ICD9, IngenixPOS, DOS = CONVERT(char(8),a.DOSDateTime,112), MemberID, Market, 'P'
FROM #PRESubmissions a
WHERE NOT EXISTS (SELECT * FROM tblSubmissions b WHERE a.HICNumber = b.HICNumber AND a.IngenixPOS = b.ProviderType AND CONVERT(char(8),a.DOSDateTime,112) = b.DOS AND a.ICD9 = b.ICD9)
--UPDATE EXISITING CLUSTERS
UPDATE a
SET a.SubmissionID = b.SubmissionID
--SELECT a.GMPI, a.ICD9, a.IngenixPOS, a.DOS, a.HICNumber, a.Market, b.SubmissionID
FROM #PRESubmissions a JOIN tblSubmissions b
ON a.HICNumber = b.HICNumber AND a.IngenixPOS = b.ProviderType AND CONVERT(char(8),a.DOSDateTime,112) = b.DOS AND a.ICD9 = b.ICD9
--GET FILENUM
UPDATE a
SET a.FileNum = b.FileNum
FROM #PRESubmissions a JOIN #Submissions b
ON a.MemberID = b.MemberID AND a.IngenixPOS = b.BillType AND a.DOS = b.FromDOS AND a.ICD9 = b.D0
UPDATE a
SET a.FileNum = b.FileNum
FROM #PRESubmissions a JOIN #Submissions b
ON a.MemberID = b.MemberID AND a.IngenixPOS = b.BillType AND a.DOS = b.FromDOS AND a.ICD9 = b.D1
UPDATE a
SET a.FileNum = b.FileNum
FROM #PRESubmissions a JOIN #Submissions b
ON a.MemberID = b.MemberID AND a.IngenixPOS = b.BillType AND a.DOS = b.FromDOS AND a.ICD9 = b.D2
UPDATE a
SET a.FileNum = b.FileNum
FROM #PRESubmissions a JOIN #Submissions b
ON a.MemberID = b.MemberID AND a.IngenixPOS = b.BillType AND a.DOS = b.FromDOS AND a.ICD9 = b.D3
UPDATE a
SET a.FileNum = b.FileNum
FROM #PRESubmissions a JOIN #Submissions b
ON a.MemberID = b.MemberID AND a.IngenixPOS = b.BillType AND a.DOS = b.FromDOS AND a.ICD9 = b.D4
UPDATE a
SET a.FileNum = b.FileNum
FROM #PRESubmissions a JOIN #Submissions b
ON a.MemberID = b.MemberID AND a.IngenixPOS = b.BillType AND a.DOS = b.FromDOS AND a.ICD9 = b.D5
UPDATE a
SET a.FileNum = b.FileNum
FROM #PRESubmissions a JOIN #Submissions b
ON a.MemberID = b.MemberID AND a.IngenixPOS = b.BillType AND a.DOS = b.FromDOS AND a.ICD9 = b.D6
UPDATE a
SET a.FileNum = b.FileNum
FROM #PRESubmissions a JOIN #Submissions b
ON a.MemberID = b.MemberID AND a.IngenixPOS = b.BillType AND a.DOS = b.FromDOS AND a.ICD9 = b.D7
UPDATE a
SET a.FileNum = b.FileNum
FROM #PRESubmissions a JOIN #Submissions b
ON a.MemberID = b.MemberID AND a.IngenixPOS = b.BillType AND a.DOS = b.FromDOS AND a.ICD9 = b.D8
--INSERT INTO HISTORY TO DOCUMENT THIS SUBMISSION
INSERT tblSubmissionHistory(SubmissionID, SubmissionBatchID, SubmissionDate, BatchStatus, FileNum)
SELECT SubmissionID, SubmissionBatchID = @SubmissionBatchID, SubmissionDate = getdate(), 'P', FileNum FROM #PRESubmissions
/*********************************/
--Select out the file data
SELECT RIGHT(RTRIM(dbo.padString(Row_Number() OVER (ORDER BY MemberID ASC),5,'0',0)) ,5) as RowNum
, MemberID
, BillType
, FromDOS
, ToDOS
, D0
, ISNULL(D1,' ') as D1
, ISNULL(D2,' ') as D2
, ISNULL(D3,' ') as D3
, ISNULL(D4,' ') as D4
, ISNULL(D5,' ') as D5
, ISNULL(D6,' ') as D6
, ISNULL(D7,' ') as D7
, ISNULL(D8,' ') as D8
, CPT
, FileNum
FROM #Submissions
Thanks in advance!!!
February 16, 2011 at 11:22 am
Well, it's the long way around the barn these days, but it's not that uncommon in SQL 2k and back. However, you always start with the inside moving out. The full script is complex and I didn't take the time to understand all the ways #PRESubmissions is getting fed. That said:
Starting with the inside:
SELECT
MAX(RowCounter) AS RowCounter,
MemberID,
DOS,
ICD9,
IngenixPOS
FROM
#PRESubmissions
GROUP BY
MemberID,
DOS,
ICD9,
IngenixPOS
HAVING
COUNT(*) > 1
based on memberID, DOS, ICD9, and IngenixPOS, find any records with more then one occurence of that group. When you do, get the MAX RowCounter.
So, with that concept as a subtable in your head ( a list of dupes with a way to find the last one of them):
--DELETE DUPLICATE CLUSTERS
DELETE FROM
t1
--select t1.*
FROM
#PRESubmissions t1
INNER JOIN
(-- Duped record list with a MAX(RowCounter)
) t2
ON(
t1.MemberID = t2.MemberID
AND t1.DOS = t2.DOS
AND t1.ICD9 = t2.ICD9
AND t1.IngenixPOS = t2.IngenixPOS
AND t1.RowCounter <> t2.RowCounter
)
It joins back on #PRESubmissions based on the four grouping fields, and then any record that ISN'T the MAX(RowCounter) field. It deletes these from t1 (#PreSubmissions), leaving you only the record with the MAX(RowCounter)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 16, 2011 at 1:33 pm
Craig,
Thank you very,very much for that explanation. That helped clear it up!
Adam
February 16, 2011 at 10:08 pm
adam spencer (2/16/2011)
Craig,Thank you very,very much for that explanation. That helped clear it up!
Adam
My pleasure, glad I could help.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply