July 5, 2012 at 9:00 am
I have the following dataset: (I excluding columns for hippa reasons)
claimid DaysInER CodeCount line code Billed paid
12172E00282 1 3 10 450 1,625.00 110.20
12172E00282 1 3 11 450 162.00 110.20
12172E00282 1 3 12 450 162.00 110.20
10322E00003 2 4 22 450 306.00 101.95
10322E00003 2 4 23 450 203.00 101.95
10322E00003 2 4 24 450 82.00 82.00
10322E00003 2 4 25 450 639.00 101.95
11152E01548 3 5 22 481 5,453.70 416.20
11152E01548 3 5 23 481 5,453.70 416.20
11152E01548 3 5 24 481 5,453.70 416.20
11152E01548 3 5 25 481 1,292.79 416.20
11152E01548 3 5 26 481 584.30 416.20
12039E04673 3 4 22 410 408.63 97.22
12039E04673 3 4 23 410 136.21 97.22
12039E04673 3 4 24 410 136.21 97.22
12039E04673 3 4 25 410 136.21 97.22
For each day in ER the rev code was only supposed to be billed once but in this case we got billed more than once. So I need to figure out how to display only the extra amount we were billed.
For example the very last claim the person spent 3 days in ER and we got billed 4 times for code 410 instead of 3, I would only want to display 1 line, not all 3 (it doesnt even matter which of the 4 lines would be displayed
I am using the following query - to get get the dataset below.(The one above is the originail dataset that I am working with in the query).
select claimid, DaysInER
, RevCodeCount, claimline, revcode,servcode
, SevCodeDescr, MedicaidID
, MemName, [Plan], provid, Provname, [status]
,okpayby, ClaimLineBilled, ClaimLinePdAmt
, TotClaimBilled,TotClaimPaidAmt
,paiddate, startdate, enddate
from (
Select e.claimid, datediff(day,c.startdate, c.enddate) +1 as DaysInER
, e1.RevCodeCount, e.claimline, e.revcode,e.servcode
,s.description as SevCodeDescr, m.secondaryid as MedicaidID
, m.fullname as MemName,pr.[description] as 'Plan', p.provid,
p.fullname as Provname, e.status
,c.okpayby, e.claimamt as ClaimLineBilled,e.amountpaid as ClaimLinePdAmt
,c.totalamt as TotClaimBilled,
c.totalpaid as TotClaimPaidAmt
,c.paiddate, c.startdate, c.enddate
,ROW_NUMBER() over (partition by e.claimid order by c.claimid) as rn
--into dbo.foDupRevCode
from V_ER_Claims e
inner join
(select claimid, revcode,COUNT(revcode) as RevCodeCount
from V_ER_Claims
where [status] <> 'DENY' and amountpaid <> 0
group by claimid,revcode
having COUNT(revcode) >1) e1
on e.claimid = e1.claimid
and e.revcode = e1.revcode
inner join qnxclaim c
on e1.claimid = c.claimid
inner join qnxprovider p
on c.provid = p.provid
inner join qnxmember m
on c.memid = m.memid
inner join qnxbenefitplan b
on c.planid =b.planid
inner join qnxsvccode s
on e.servcode = s.codeid
inner join qnxprogram pr
on b.programid = pr.programid
left join V_ReversalsAndOrigClaims r
on left(e.claimid,11) = left(r.adj,11)
where (e.revcode not between '0300' and '0339')
and (e.revcode not between '0250' and '0269')
and (billclasscode = '3')
and (c.status in ('PAID', 'REVERSED'))
and (r.claim is null)
and e1.RevCodeCount > datediff(day,c.startdate, c.enddate) +1
and e.status <> 'DENY'
AND e.claimamt <> 0
) as x
where
rn<=(revcodecount-daysiner)
order by claimid, revcode
which I thought worked at first but then I noticed this claims but it is not including all the lines for example:
claimid DaysInER RevCount claimline revcode Billed paid
10333E01043 3 10 24 636 188.00 196.22
10333E01043 3 10 25 636 188.00 196.22
10333E01043 3 10 26 636 35.00 196.22
10333E01043 3 10 29 636 254.00 196.22
10333E01043 3 10 31 636 3.00 196.22
10333E01043 3 10 30 636 3.00 196.22
in this case revcount - daysiner = 7 but I am only getting six claim lines
and
I am also noticing from the claim example in the previous post, that revcode 450 isnt showing in the results there are 5 claimlines that were paid but there should have only been 3, so I should have 2 450 claimlines in the results
July 5, 2012 at 9:17 am
I thought my logic was somewhat close, but after looking at my results, I can see that I am missing a lot of records.
July 5, 2012 at 10:09 am
Hi and welcome to SSC. It seems that you still have an unanswered question or two? It will greatly increase your chances of getting help from people if you post information to make it easy to help. This means ddl (create table statements), sample data (insert statements) and desired output. Of course make sure your sample data is sufficiently sanitized or totally made up. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 5, 2012 at 11:42 am
I was able to figure out the problem:
it was
ROW_NUMBER() over (partition by e.claimid,e.revcode order by c.claimid)
I originally had ROW_NUMBER() over (partition by e.claimid order by c.claimid)
July 5, 2012 at 11:44 am
Thanks Sean, I will be sure to follow the etiquette when posting in questions in the future.
Sorry about that.
July 5, 2012 at 11:56 am
freida1973 (7/5/2012)
Thanks Sean, I will be sure to follow the etiquette when posting in questions in the future.Sorry about that.
No worries at all. It just helps when you put yourself in the shoes of the people trying to help. With that type of info it is impossible.
Glad you got it worked and thanks for posting back.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply