Combine sql Queries

Question

Combine sql Queries

ry.sqldev

SSC Eights!

Points: 882
More actions
October 22, 2010 at 6:19 pm

#230782

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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. ..

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

ry.sqldev SSC Eights! Points: 882 More actions · Answer 2

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.

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 3

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ry.sqldev SSC Eights! Points: 882 More actions · Answer 4

ry.sqldev

SSC Eights!

Points: 882

October 23, 2010 at 3:17 pm

#1241311

will try to do that.thanks for suggestion

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 6

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

lnoland SSCommitted Points: 1753 More actions · Answer 7

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

ry.sqldev SSC Eights! Points: 882 More actions · Answer 8

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