September 25, 2008 at 12:41 pm
I have the following code because I'm transposing diagnoses from a one per record setting to be columns in the output record. I'm using a While loop to do this. Can I use a Tally table if I'm performing multiple operations within the one iteration? I can't figure out how to do it if so.
Or maybe I'm just thinking this through incorrectly, and there's a better way.
It works, and it improves vastly over the manual process that was in place before, so I'm already a hero, I just wanted to see if I could make it better.
Thanks in advance . . .
DECLARE @diagnoses varchar(8000),
@maxRows int,
@currentRow int
SET @diagnoses = ''
IF object_id('Tempdb..#hms') IS NOT NULL BEGIN DROP TABLE #hms END
CREATE TABLE #hms (iRow int identity(1,1),
firstname varchar(50),
lastname varchar(50),
mcid varchar(15),
dob datetime,
ssn varchar(11),
startdate datetime,
enddate datetime)
IF object_id('Tempdb..#claims') IS NOT NULL BEGIN DROP TABLE #claims END
CREATE TABLE #claims(iRow int identity(1,1),
claimtype char(3),
claimid varchar(15),
startdate datetime,
enddate datetime,
servcode varchar(5),
member varchar(150),
ssn varchar(11),
medicaidid varchar(50),
providerfirst varchar(150),
providerlast varchar(150),
billed money,
paid money,
status varchar(25),
primaryDx varchar(6),
diagnoses varchar(1000))
IF object_id('Tempdb..#claimdiag') IS NOT NULL BEGIN DROP TABLE #claimdiag END
CREATE TABLE #claimdiag(iRow int identity(1,1),
claimid varchar(15),
diagtype varchar(10),
codeid varchar(7),
sequence int)
IF object_id('Tempdb..#claimdiag2') IS NOT NULL BEGIN DROP TABLE #claimdiag2 END
CREATE TABLE #claimdiag2(iRow int identity(1,1),
claimid varchar(15),
diagtype varchar(10),
codeid varchar(7),
sequence int)
-- load #hms table
INSERT INTO #hms (firstname, lastname, mcid,dob,ssn,startdate,enddate) VALUES ('doe','john','012345678912','01/01/1978','012345678','1/1/2008','12/31/2078')
INSERT INTO #hms (firstname, lastname, mcid,dob,ssn,startdate,enddate) VALUES ('doe','jane','219876543210','01/01/1978','876543210','1/1/2008','12/31/2078')
-- load #claims table
INSERT INTO #claims (claimtype, claimid, member, medicaidid, providerfirst, providerlast, startdate, enddate, billed, paid, status, ssn, servcode)
VALUES ('FFS','012345678912345','doe, john','012345678912','jack','darippa','1/1/2008','1/2/2008',100.00,50.00,'paid','012345678','36415')
INSERT INTO #claims (claimtype, claimid, member, medicaidid, providerfirst, providerlast, startdate, enddate, billed, paid, status, ssn, servcode)
VALUES ('FFS','012345678912346','doe, john','012345678912','jack','darippa','1/1/2008','1/2/2008',100.00,50.00,'paid','012345678','36415')
INSERT INTO #claims (claimtype, claimid, member, medicaidid, providerfirst, providerlast, startdate, enddate, billed, paid, status, ssn, servcode)
VALUES ('ENC','012345678912347','doe, john','012345678912','jack','darippa','1/1/2008','1/2/2008',100.00,50.00,'paid','012345678','36415')
INSERT INTO #claims (claimtype, claimid, member, medicaidid, providerfirst, providerlast, startdate, enddate, billed, paid, status, ssn, servcode)
VALUES ('ENC','012345678912348','doe, john','012345678912','jack','darippa','1/1/2008','1/2/2008',100.00,50.00,'paid','012345678','36415')
INSERT INTO #claims (claimtype, claimid, member, medicaidid, providerfirst, providerlast, startdate, enddate, billed, paid, status, ssn, servcode)
VALUES ('RX ','012345678912349','doe, john','012345678912','jack','darippa','1/1/2008','1/2/2008',100.00,50.00,'paid','012345678','36415')
INSERT INTO #claims (claimtype, claimid, member, medicaidid, providerfirst, providerlast, startdate, enddate, billed, paid, status, ssn, servcode)
VALUES ('RX ','012345678912340','doe, john','012345678912','jack','darippa','1/1/2008','1/2/2008',100.00,50.00,'paid','012345678','36415')
INSERT INTO #claims (claimtype, claimid, member, medicaidid, providerfirst, providerlast, startdate, enddate, billed, paid, status, ssn, servcode)
VALUES ('FFS','012345678912300','doe, jane','219876543210','lizzie','borden','1/1/2008','1/2/2008',100.00,50.00,'paid','876543210','36415')
INSERT INTO #claims (claimtype, claimid, member, medicaidid, providerfirst, providerlast, startdate, enddate, billed, paid, status, ssn, servcode)
VALUES ('FFS','012345678912301','doe, jane','219876543210','lizzie','borden','1/1/2008','1/2/2008',100.00,50.00,'paid','876543210','36415')
INSERT INTO #claims (claimtype, claimid, member, medicaidid, providerfirst, providerlast, startdate, enddate, billed, paid, status, ssn, servcode)
VALUES ('ENC','012345678912302','doe, jane','219876543210','lizzie','borden','1/1/2008','1/2/2008',100.00,50.00,'paid','876543210','36415')
INSERT INTO #claims (claimtype, claimid, member, medicaidid, providerfirst, providerlast, startdate, enddate, billed, paid, status, ssn, servcode)
VALUES ('RX ','012345678912303','doe, jane','219876543210','lizzie','borden','1/1/2008','1/2/2008',100.00,50.00,'paid','876543210','36415')
-- load #claimdiag
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912345','Primary','250.00',1)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912345','Secondary','401.7',2)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912345','Secondary','123.45',3)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912345','Secondary','543.21',4)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912346','Primary','250.01',1)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912347','Primary','250.02',1)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912347','Secondary','250.03',2)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912348','Primary','250.04',1)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912348','Secondary','250.05',2)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912348','Secondary','250.06',3)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912300','Primary','250.09',1)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912301','Primary','250.10',1)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912301','Secondary','250.11',2)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912302','Primary','250.12',1)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912302','Secondary','250.13',2)
INSERT INTO #claimdiag (claimid,diagtype,codeid,sequence) VALUES ('012345678912302','Secondary','250.14',3)
-- load #claimdiag2 - basically same as #claimdiag, no Primary/Secondary notation, have to use Sequence column
INSERT INTO #claimdiag2 (claimid,diagtype,codeid,sequence) VALUES ('012345678912349','','250.07',1)
INSERT INTO #claimdiag2 (claimid,diagtype,codeid,sequence) VALUES ('012345678912340','','250.08',1)
INSERT INTO #claimdiag2 (claimid,diagtype,codeid,sequence) VALUES ('012345678912303','','250.15',1)
INSERT INTO #claimdiag2 (claimid,diagtype,codeid,sequence) VALUES ('012345678912303','','250.16',2)
INSERT INTO #claimdiag2 (claimid,diagtype,codeid,sequence) VALUES ('012345678912303','','250.17',3)
-- ================= UPDATE DIAGNOSES ===========================
SET @maxRows = (SELECT max(iRow)+1 FROM #claims)
SET @currentRow = 1
WHILE @currentRow<@maxRows
BEGIN
UPDATE #claims SET primaryDx = rtrim(ltrim(#claimdiag.codeid))--isnull(#claimdiag.codeid,#claimdiag2.codeid)))
FROM #claimdiag
JOIN #claims ON #claimdiag.claimid = #claims.claimid AND #claimdiag.diagtype ='Primary'
-- I thought maybe I could left join to the second diag table and pull in those too using ISNULL above, not working
--LEFT JOIN #claimdiag2 ON #claimdiag2.claimid = #claims.claimid AND #claimdiag2.sequence = 1
WHERE #claims.iRow = @currentRow
-- So now I'm doing the same thing over again with the second claimdiag table
UPDATE #claims SET primaryDx = rtrim(ltrim(#claimdiag2.codeid))
FROM #claimdiag2
JOIN #claims ON #claimdiag2.claimid = #claims.claimid
WHERE #claims.iRow = @currentRow AND #claimdiag2.sequence = 1
SELECT @diagnoses = (@diagnoses + rtrim(ltrim(#claimdiag.codeid))+char(9))--isnull(#claimdiag.codeid,#claimdiag2.codeid)))+char(9))
FROM #claimdiag
JOIN #claims on #claimdiag.claimid = #claims.claimid AND #claimdiag.diagtype = 'Secondary'
-- I thought maybe I could left join to the second diag table and pull in those too, not working
--LEFT JOIN #claimdiag2 ON #claimdiag2.claimid = #claims.claimid AND #claimdiag2.diagtype = 'Secondary'
WHERE #claims.iRow = @currentRow AND #claims.claimtype IN ('FFS','ENC')
-- moved the inequality from the update above into the WHERE to avoid a lousy join, but it isn't working anyway, see above
--AND #claimdiag2.sequence > 1
ORDER BY #claimdiag.sequence
SELECT @diagnoses = (@diagnoses + rtrim(ltrim(#claimdiag2.codeid))+char(9))--isnull(#claimdiag.codeid,#claimdiag2.codeid)))+char(9))
FROM #claimdiag2
JOIN #claims on #claimdiag2.claimid = #claims.claimid
WHERE #claims.iRow = @currentRow AND #claims.claimtype = 'RX '
-- moved the inequality from the update above into the WHERE to avoid a lousy join
AND #claimdiag2.sequence > 1
ORDER BY #claimdiag2.sequence
IF len(@diagnoses)>0
SELECT @diagnoses = substring(@diagnoses,1,len(@diagnoses)-1)
UPDATE #claims SET diagnoses = substring(@diagnoses,1,1000)
WHERE iRow = @currentRow
SET @currentRow = @currentRow + 1
SET @diagnoses = ''
END
-- ==============OUTPUT=========================
SELECT isnull(claimtype,'') "Type",
ISNULL(claimid,'None Found') "Claim",
#hms.firstname "First Name",
#hms.lastname "Last Name", --member "Member",
#hms.ssn "SSN",
ISNULL(medicaidid,'') "Medicaid ID",
ISNULL(providerfirst,'') "Provider First Name",
ISNULL(providerlast,'') "Provider Last Name",
ISNULL(convert(varchar(15),#claims.startdate,110),'') "Start Date",
--convert(varchar(15),enddate,110) EndDate,
ISNULL(servcode,'') "CPT/HCPCS",
ISNULL('$'+convert(varchar(10),billed,1),'') "Billed",
ISNULL('$'+convert(varchar(10),paid,1),'') "Paid",
--status,
ISNULL(primaryDx,'') "Primary Dx",
ISNULL(diagnoses,'') "Secondary Dx"
FROM #claims
RIGHT JOIN #hms ON #claims.ssn = #hms.ssn
ORDER BY #hms.lastname, #hms.firstname, claimid
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 26, 2008 at 6:11 am
If you can create a function, you remove the need for a loop, or a tally substitute.
Actually, I created two, append 2 to one of them
CREATE FUNCTION fn_ReturnDiagnosisies
(@p1 varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
declare @Diagnosisies varchar(8000)
Select @Diagnosisies = COALESCE(@Diagnosisies+char(9) , '') + claimdiag.codeid from claimdiag
where claimdiag.claimId = @p1
RETURN @Diagnosisies
END
GO
Then you just do this.
UPDATE claims SET primaryDx = rtrim(ltrim(claimdiag.codeid))--isnull(claimdiag.codeid,claimdiag2.codeid)))
FROM claimdiag
JOIN claims ON claimdiag.claimid = claims.claimid AND claimdiag.diagtype ='Primary'
-- So now I'm doing the same thing over again with the second claimdiag table
UPDATE claims SET primaryDx = rtrim(ltrim(claimdiag2.codeid))
FROM claimdiag2
JOIN claims ON claimdiag2.claimid = claims.claimid
WHERE
claimdiag2.sequence = 1
-- using the len function as it needs to chop off the first one tha is in the primary column
-- theres WILL be a better way of doing this
UPDATE claims SET diagnoses = right(dbo.fn_ReturnDiagnosisies(claimdiag.claimId), len(dbo.fn_ReturnDiagnosisies(claimdiag.claimId)) - 7)
FROM claimdiag
JOIN claims on claimdiag.claimid = claims.claimid AND claimdiag.diagtype = 'Secondary'
WHERE claims.claimtype IN ('FFS','ENC')
UPDATE claims SET diagnoses = right(dbo.fn_ReturnDiagnosisies2(claimdiag2.claimId), len(dbo.fn_ReturnDiagnosisies2(claimdiag2.claimId)) - 7)
FROM claimdiag2
JOIN claims on claimdiag2.claimid = claims.claimid
WHERE claims.iRow = @currentRow AND claims.claimtype = 'RX '
AND claimdiag2.sequence > 1
HTH
4 updates, but it seems to match your test data
Dave J
September 26, 2008 at 6:31 am
Normally, that would solve my problem, but I can't create a function. Only temp objects. DBAs have the system locked down pretty tight, still arguing with them over whether they can grant me stuff like this.
Thanks, though.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 26, 2008 at 7:46 am
September 26, 2008 at 8:03 am
Adrian Nichols (9/26/2008)
Hi,Is there a defined maximum or a reasonably assumable maximum sequence number?
upper limit of 23 in the data, although I'm honestly not sure how they snuck that many in. I think the file specs say 12.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 26, 2008 at 8:09 am
That's okay then, the attached file contains a solution using Dynamic-SQL to pivot the data so that the codeIDs from sequences greater than 1 are concatenated. It'll work for up to a sequenceID of 40 and then runs out of space in the VARCHAR(8000) command.
It rids the loop but I can't say how great the performance is going to be on larger data sets. :ermm:
September 26, 2008 at 8:28 am
Adrian Nichols (9/26/2008)
That's okay then, the attached file contains a solution using Dynamic-SQL to pivot the data so that the codeIDs from sequences greater than 1 are concatenated. It'll work for up to a sequenceID of 40 and then runs out of space in the VARCHAR(8000) command.It rids the loop but I can't say how great the performance is going to be on larger data sets. :ermm:
Thanks, nice filename on the attachment 😉
I'll run a couple and see how it does comparatively.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 28, 2008 at 8:28 pm
jcrawf02 (9/26/2008)
Normally, that would solve my problem, but I can't create a function. Only temp objects.
Are you creating a stored procedure or just a script? I need to know this because there are some tricks you can do to get around such a lockdown. Also, will only 1 person be running this at a time? Again, I need to know for "tricks".
And, I don't really think a Tally table is the way to do this one. I know... sounds strange coming from me 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 29, 2008 at 8:09 am
Jeff Moden (9/28/2008)
jcrawf02 (9/26/2008)
Normally, that would solve my problem, but I can't create a function. Only temp objects.Are you creating a stored procedure or just a script? I need to know this because there are some tricks you can do to get around such a lockdown. Also, will only 1 person be running this at a time? Again, I need to know for "tricks".
And, I don't really think a Tally table is the way to do this one. I know... sounds strange coming from me 😛
Creating a script, and we already know you're strange, it's ok . . .;)
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 30, 2008 at 4:49 am
Sorry for the delay...
Not everything that goes into TempDB has to begin with a "#" sign... you can use TempDB just like any other database. In other words, if the DBA's won't allow you to create a permanent function to do your work, turn what you have as #Temp tables into regular tables, but create them in TempDB. Also, create a function to do the necessary concatenation in TempDB just as if you owned the place. When your script is complete, just make sure that you delete everything you created, and no one is the wiser... 😉 It's cheating like hell, but it works. First statement in your script should be "USE TEMPDB". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply