October 22, 2010 at 6:19 pm
Hi
I am stuggling to combine this 5 queries in to one query.plz help me with this
select c.claimid, cd.claimline, cd.status
from claim c (NOLOCK) join claimdetail cd on c.claimid = cd.claimid
where formtype = 'UB92'
and cd.termid = 0
and cd.status <> 'DENY'
and facilitycode + billclasscode in ('12', '13', '14', '22', '23', '85')
and c.status = 'PAY'
--bypass manually priced claims
and not exists
(
select * from claimedit
where claimid = c.claimid
and ruleid IN ('917', '921')
)
--bypass COB claims
and totextpaidamt = 0
--bypass claims with claimdocument 'QTX0007710149' No OOPE/Copay Applied
and not exists
(
select * from claimdocument
where documentid = 'QTX0007710149' and claimid = c.claimid
)
2)
SELECT c.claimid,cd.claimline,cd.benefitamt,c.okpayby,cd.status
FROM claim c (NOLOCK) JOIN claimdetail cd (NOLOCK) ON c.claimid = cd.claimid
WHERE cd.status = 'DENY' AND cd.benefitamt <> 0
AND c.status IN ('PAY','DENY','ADJUCATED','REV','REVSYNCH')
3)
SELECT DISTINCT c.claimid, p2.fullname FROM claim c (NOLOCK)
JOIN claimdetail cd (NOLOCK) ON c.claimid = cd.claimid
JOIN contractterm ct (NOLOCK) ON cd.contractid = ct.contractid and cd.termid = ct.termid
JOIN provider p1 (NOLOCK) ON c.provid = p1.provid
JOIN affiliation a (NOLOCK)ON p1.provid = a.provid
JOIN provider p2 (NOLOCK) ON p2.provid = a.affiliateid
WHERE c.status IN ('PAY','REV') AND c.reimbursemember = 'N'
AND ct.fundid = 'QTXID78291' AND a.payflag <> 0
4)
SELECT cd.claimid, MAX(c.totalpaid) AS totalpaid, sum(cd.contractpaid) AS eligibleamt
FROM claim c WITH (NOLOCK)
JOIN claimdetail cd WITH (NOLOCK) ON c.claimid = cd.claimid
WHERE c.status IN ('PAY','DENY') AND cd.status <> 'DENY'
GROUP BY cd.claimid HAVING max(c.totalpaid) > sum(cd.contractpaid)
5)
select ca.*,c.status
FROM
(Select max(op_date)dt,claimid,
ind='-From '+rtrim(orig_Status)+' to '+rtrim(New_STATUS)
from claim_audit (nolock) CA
where
orig_Status IN ('PAID','DENIED','REVERSED','VOID')
AND New_STATUS NOT IN ('PAID','DENIED','REVERSED','VOID')
Group by claimid,orig_Status,New_Status)CA
INNER JOIN claim C (nolock)
ON CA.CLAIMID=C.CLAIMID
where dt > getdate() - 7
October 22, 2010 at 7:17 pm
You might receive some assistances if you defined your table(s), provided some sample data and desired results. Please refer to the fist link in my signature block for what to do to receive tested help. You have provided your sample code, but it would be easier for some one to test it if it had been enclosed in the proper IFCode Shortcuts. ..
October 22, 2010 at 7:30 pm
we use to run these queries and prepare a excel report for the users.but now we need to combine this queries, i mean to say that they just want to make it as one query sothat we can use one dataset and create a ssrs report.this is the requirement.they dont want multiple datasets.claimid is the unique field.which we are retriving in all the queries.
October 23, 2010 at 5:00 am
The best suggestion I can give you is, instead of focusing on combining these five procedures, focus on defining the single query that will bring back what you need. Just looking at these five, there's all kinds of apparent overlap. Trying to discern which things have to be there from the things that don't could make you nuts. Instead, go a blank screen and start defining from scratch what the users need.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 23, 2010 at 3:17 pm
will try to do that.thanks for suggestion
October 24, 2010 at 12:24 pm
I'm not sure why everyone thinks this stuff has to be done in a single query. And, it probably should NOT be done in a single query. Create a temp table (or two) and populate it in pieces. The end result will likely be faster than a single query, anyway, especially if you use the temp table in joins to limit the number of rows you need to look at.
--Jeff Moden
October 25, 2010 at 6:01 am
Jeff Moden (10/24/2010)
I'm not sure why everyone thinks this stuff has to be done in a single query. And, it probably should NOT be done in a single query. Create a temp table (or two) and populate it in pieces. The end result will likely be faster than a single query, anyway, especially if you use the temp table in joins to limit the number of rows you need to look at.
Hey Jeff. Of course, you could be 100% right, but looking at all the combined info there, it does seem likely that you could get a single query out of it, but not by simply combining all 5. In that case you'd definitely be 100% right. W/O completely carving the whole thing up myself, I just can't say for either way.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 25, 2010 at 9:31 am
Jeff Moden (10/24/2010)
I'm not sure why everyone thinks this stuff has to be done in a single query. And, it probably should NOT be done in a single query. Create a temp table (or two) and populate it in pieces. The end result will likely be faster than a single query, anyway, especially if you use the temp table in joins to limit the number of rows you need to look at.
When I first started to use SQL Server I always tried to do everything in one query, too. I know why I did -- I'd presumed that if I created one query that put out the output that I wanted, the optimizer would understand the objective and would create temporary tables, etc., as needed, to make things efficient. I figured if I split it up into pieces the optimizer might think I had multiple objectives and would fail to do things efficiently. After studying a lot of execution plans it became clear that the optimizer often didn't understand my objective very well anyway and I was betting on the wrong horse.
- Les
October 25, 2010 at 1:32 pm
Hey Thanks Guys,
I was in confusion that whether i can combine it or not.as you said better to use Temp table,how can i use it in my case
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply