November 4, 2008 at 4:53 am
Hi gurus,
I have 2 tables --
Table 1
CLAIM ID ACTIVITY CODE ACTAMOUNTDenial Code
55041-000412 0 C01
55041-000414653 18 NULL
55041-000410 130 NULL
100991-000176856 230 NULL
100991-000181001 0 A01
100991-000187209 25 P01
100991-000176700 230 NULL
Table 2 -
Denial CodeDescription
A01 Administrative information missing
C01 Clinical information Missing
E01 Eligibility
P01 Price List related
THe desired output is ---
CLAIMID TOTAL AMT REJECTION REASON
55041-0004148 Clinical information Missing
100991-0001485Administrative information missing +
Price List Related.
Can some one giude me how to do this.When I group comes as separate line.
Thanks a lot.
November 4, 2008 at 5:22 am
mathewspsimon (11/4/2008)
Hi gurus,I have 2 tables --
Table 1
CLAIM ID ACTIVITY CODE ACTAMOUNTDenial Code
55041-000412 0 C01
55041-000414653 18 NULL
55041-000410 130 NULL
100991-000176856 230 NULL
100991-000181001 0 A01
100991-000187209 25 P01
100991-000176700 230 NULL
Table 2 -
Denial CodeDescription
A01 Administrative information missing
C01 Clinical information Missing
E01 Eligibility
P01 Price List related
THe desired output is ---
CLAIMID TOTAL AMT REJECTION REASON
55041-0004148 Clinical information Missing
100991-0001485Administrative information missing +
Price List Related.
Can some one giude me how to do this.When I group comes as separate line.
Thanks a lot.
Try this:
select ClaimID, sum(ActAmount), Description as [Rejection Reason]
from table1 t1
left outer join table2 t2 on t2.DenialCode= t1.DenialCode
group by ClaimID, Description
November 4, 2008 at 6:47 am
yes i tried that but it gives the following as output
CLAIMID TOTAL AMT REJECTION REASON
55041-0004 0 Clinical information Missing
55041-0004 148 NULL
100991-0001 0 Administrative information missing
100991-0001 25 Price List Related
100991-0001 460 NULL
November 4, 2008 at 6:59 am
Does this work?
select ClaimID, TotalAmt, Description
from
(select ClaimID, sum(ActAmount)
from table1
group by ClaimID) as Total
inner join table1 t1 on t1.ClaimID = Total.ClaimID
left outer join table2 t2 on t2.DenialCode = t1.DenialCode
November 4, 2008 at 8:44 pm
Sir this does not work either.the description can be different for each claim id.so i gues it has be concatenated or what?.i think pivot can be used but my db is 2005 but since it is migrated from sql 2000 so most of the features are that of sql 2000.May be case can be used but i am not sure how so I posed this question.
November 5, 2008 at 3:55 am
can some help me in this please
November 5, 2008 at 5:16 am
Try this:
select Total.ClaimID, TotalAmt, Description
from
(select ClaimID, sum(Amount) as TotalAmt
from table1
group by ClaimID) as Total
inner join table1 t1 on t1.ClaimID = Total.ClaimID and DenialCode is not null
left outer join table2 t2 on t2.DenialCode = t1.DenialCode
Now you'll get two different rows for ClaimID 100991-0001 but that's because you've got two different rejection reasons for it.
November 5, 2008 at 6:36 am
hi karl,
yes i know that and my objective is to achieve it as single row.can these be concatenated and shown in the output? this columns output need to be dynamically concatenated.
November 5, 2008 at 8:57 am
What you're seeking can be rather complex, but there are some things that might simplify it, depending on the overall objective. How is this data to be presented to the person requesting it? If it's web-based, you could easily handle multiple records for the same ID with procedural code in the web page. If the objective is to just generate a printed report, then I wonder if it would be important for the data viewer to know whether or not there was any part of the claim that WASN'T denied, so as to be able to differentiate between claim data that's a partial denial vs. a complete one. Also, is the number of possible denial reasons limited? Or, if there are, say, a large number of denial reasons for a given ID, do they really need to see them all?
Once you ask these questions of your data requester (or of yourself if you already know the answers), let us know. There are many ways to achieve this, but knowing what data is important and what isn't might go a long way toward making this as simple as possible.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 5, 2008 at 10:06 am
If you don't mind using a user defined function, you could create one like this:
CREATE FUNCTION dbo.udf_ClaimDenialReasons
(
@ClaimId INT
)
RETURNS VARCHAR
AS
BEGIN
DECLARE @Description VARCHAR(4000)
SELECT @Description = CASE WHEN @Description IS NOT NULL THEN @Description + ', ' + Description
ELSE Description END
FROM table1 AS t1
INNER JOIN table2 AS t2
ON t2.Denial_Code = t1.Denial_Code
WHERE t1.ClaimId = @ClaimId
RETURN @Description
END
And then yoiur SQL statement would be:
SELECT ClaimID,
SUM(ActAmount) AS TotalAmt,
dbo.udf_ClaimDenialReasons(ClaimId) AS DenialReasons
FROM table1
GROUP BY ClaimID
November 6, 2008 at 1:48 am
Hi sean,
your solution is good,at least it gives one outpurt/claim id but the problem is the description seems appear parially.you what I mean is instead of "Clinical .. ' to appear only C appears also for other denial description.Can u suggest how i can overcome this.
56703-0010895.5NULL
93686-000385.98NULL
23441-00360C
99925-0001195A
47013-0005650.49C
98426-0002177P
75392-0015115NULL
November 6, 2008 at 8:59 am
It seems I had a small bug in the udf code I posted. The return statement of the udf should be RETURN VARCHAR(4000) instead of just RETURN VARCHAR. It should work properly once you update that statement.
November 6, 2008 at 7:54 pm
You can use the STUFF function instead of creating your own function.
Like this:
-- first create some sample tables with data
DECLARE @Table1TABLE
(
ClaimID VARCHAR(20)
, ActivityCode INT
, ActAmount INT
, DenialCode CHAR(3)
)
INSERT INTO @Table1
SELECT '55041-0004', 12, 0, 'C01'
UNION ALL SELECT '55041-0004',14653,18,NULL
UNION ALL SELECT '55041-0004',10,130 , NULL
UNION ALL SELECT '100991-0001',76856 , 230,NULL
UNION ALL SELECT '100991-0001',81001, 0 ,'A01'
UNION ALL SELECT '100991-0001',87209, 25 ,'P01'
UNION ALL SELECT '100991-0001',76700, 230 ,NULL
DECLARE @Table2TABLE
(
DenialCode CHAR(3)
,[Description] VARCHAR(50)
)
INSERT INTO @Table2
SELECT 'A01' ,'Administrative information missing'
UNION ALL SELECT 'C01', 'Clinical information Missing'
UNION ALL SELECT 'E01', 'Eligibility'
UNION ALL SELECT 'P01','Price List related'
--first get all of the fields we need into a cte
-- this is just to make our code cleaner
;WITH All_Info AS
(
SELECT ClaimID
, ActAmount
, [Description]
FROM @Table1 t1
LEFT JOIN @Table2 t2 on t2.DenialCode= t1.DenialCode
)
-- now compute our data
-- add all the account amounts per claim id
-- use the stuff function to concatenate the description
SELECT ClaimID
, SUM(ActAmount) AS TotalAmount
, STUFF(
(SELECT ' ' + [Description] + ','
FROM All_Info B
WHERE A.ClaimID = B.ClaimID
FOR XML PATH('') )
,1
,1
,'') AS RejectionReason
FROM All_Info A
GROUP BY ClaimID
BTW, you'll get better answers if you post DDL and sample data like I did in this example 😉
November 7, 2008 at 10:10 pm
thanks grabber.it works.
November 8, 2008 at 4:38 pm
Great! Thanks for the feedback, Mathew 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply