Bottom 100?

I have the below - which effectively takes the top 100 accounts and lists them with the greatest to lowest variance between mcare_reimb and reimb_amt - is there a way to get the bottom 100 accounts as well?  Those with the lowest possible variance, etc. without being null or zero in those field?

select run_id, headid, sum(mcare_reimb) as mcare, sum(reimb_amt) as reimb

into #troubleaccts2

from dbo.CM_tblContractAnalysis_Detail

where run_id = 166 and isnull(reimb_amt,'') <> 0 and isnull(mcare_reimb,0) <> 0 

group by run_id,headid

order by sum(mcare_reimb) - sum(reimb_amt) desc



; with HEAD as ( SELECT top 100 ROW_NUMBER() OVER ( ORDER BY r.mcare - r.reimb desc) as RowID, 

r.mcare, r.reimb,h.HeadID, h.RUN_ID, h.contractID, h.ACCT_NO, h.INSURANCE, h.PTP, h.PATIENT_TP, 

h.ADMIT, h.DISCHARGE, h.CHARGES, h.PYMTS, h.ADJ, h.DRG, h.MCARE_REIMB

FROM web5.dbPDE.dbo.CM_tblContractAnalysis_Head AS h inner join

#troubleaccts2 as r on r.RUN_ID = h.RUN_ID and r.headid = h.headid )

SELECT h.RowID, h.HeadID, h.RUN_ID, h.contractID, h.ACCT_NO, h.INSURANCE,m.Ins_name, h.PTP, h.PATIENT_TP, 

h.ADMIT, h.DISCHARGE, h.CHARGES, h.PYMTS, h.ADJ, h.DRG, d.MCARE_REIMB,

d.DEPT_CODE, d.PROC_CODE, d.CPT_CODE, d.REV_CODE, d.TRANS_DT, d.QTY, d.REV, 

d.REIMB_METHOD, d.QTY * d.REIMB_AMT AS REIMB_AMT, d.REIMB_MATCH, d.REIMB_PERCENT, d.REIMB_NOTES

FROM web5.dbPDE.dbo.CM_tblContractAnalysis_Detail AS d INNER JOIN

HEAD AS h ON d.RUN_ID = h.RUN_ID and d.HeadID = h.HeadID INNER JOIN 

dbo.tblMAP_INS AS m ON h.INSURANCE = m.INSURANCE 

order by h.RowID, d.trans_dt, Abs(d.mcare_reimb - ( d.QTY * d.REIMB_AMT)) desc

Open in new window

tbaseflugAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee W, MVPTechnology and Business Process AdvisorCommented:
Short answer... why not change the Order By to be in reverse
0
Rajkumar GsSoftware EngineerCommented:
leew said right!

For eg:
SELECT Top 100 * FROM urTable
ORDER BY ID DESC

Raj
0
tbaseflugAuthor Commented:
Well, right now - it brings back the above, I get something like:

RowID       mcare       reimb
1               500           2200
1               1000         2000
1               1200         1800
2               500           2200
2               1000         2000
2               1200         1800
3               500           2200
3               1000         2000
3               1200         1800

But when I flip the sort order the mcre to reimb is flipped - but same accounts come up?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Tim HumphriesDirectorCommented:
Looks like you've only changed the order on Abs(d.mcare_reimb - ( d.QTY * d.REIMB_AMT)), but are still ordering by RowID - you need to place the calculation as  the first element in  your sort order.

Tim
0
tbaseflugAuthor Commented:
tim - can you give me an example?  Thanks!
0
Tim HumphriesDirectorCommented:
Your order by clause is currently:

order by h.RowID, d.trans_dt, Abs(d.mcare_reimb - ( d.QTY * d.REIMB_AMT)) desc

this will order by rowid first, so changing the 'desc' to 'asc' or removing it will make no difference as it will only affect the Abs(d.mcare_reimb - ( d.QTY * d.REIMB_AMT)) calculation.

Change your order by clause to :

order by Abs(d.mcare_reimb - ( d.QTY * d.REIMB_AMT)), h.RowID, d.trans_dt

(I can't actually see the benefit of ordering by RowID in this situation - you may want to drop it altogether)

p.s. this looks like a supplementary question to one you posted a couple of days ago  - is the other question completed now?

0
tbaseflugAuthor Commented:
That looks like it will mostly work - the reason that I am listing it rowID is that the accounds are "grouped" by similar rowIDs - so when the user sees the report they see all accounts listed togther, etc.  With the above - the accounts/rowIDs are no longer ordered "togther"
0
Tim HumphriesDirectorCommented:
well it depends whether they want to see accounts ordered together, or whether seeing the variance between mcare_reimb and reimb_amt is more important. You can't have both at the same time...
0
tbaseflugAuthor Commented:
Ok - that is where I am at a loss - in my original query - it lists it by rowid/acct - then within that "group" of accounts, it then sorts the variance - so, if I am reading you correctly - that will work with top 100 but not how I need it under the pretense of bottom 100, etc?

RowID       mcare       reimb
1               500           2200
1               1000         2000
1               1200         1800
2               500           2200
2               1000         2000
2               1200         1800
3               500           2200
3               1000         2000
3               1200         1800
0
tbaseflugAuthor Commented:
Is there a way - in the intial select - to only pull those records with the minimum variance between mcare and reimb - from the following code?

select run_id, headid, sum(mcare_reimb) as mcare, sum(reimb_amt) as reimb
into	#troubleaccts2
from dbo.CM_tblContractAnalysis_Detail
where run_id = 166 and isnull(reimb_amt,'') <> 0 and isnull(mcare_reimb,0) <> 0 
group by run_id,headid
order by sum(mcare_reimb) - sum(reimb_amt) desc

Open in new window

0
Tim HumphriesDirectorCommented:
Yes - if I'm understanding you right, you can simply use:

select top 100 run_id, headid, sum(mcare_reimb) as mcare, sum(reimb_amt) as reimb
into    #troubleaccts2
...

0
SharathData EngineerCommented:
Can you check this?
SELECT   run_id, 
         headid, 
         Sum(mcare_reimb) AS mcare, 
         Sum(reimb_amt)   AS reimb 
INTO     #troubleaccts2 
FROM     dbo.cm_tblcontractanalysis_detail 
WHERE    run_id = 166 
         AND Isnull(reimb_amt,'') <> 0 
         AND Isnull(mcare_reimb,0) <> 0 
GROUP BY run_id, 
         headid 
ORDER BY Sum(mcare_reimb) - Sum(reimb_amt) DESC; 

WITH head 
     AS (SELECT Row_number() 
                  OVER(ORDER BY r.mcare - r.reimb) AS rowid, 
                r.mcare, 
                r.reimb, 
                h.headid, 
                h.run_id, 
                h.contractid, 
                h.acct_no, 
                h.insurance, 
                h.ptp, 
                h.patient_tp, 
                h.admit, 
                h.discharge, 
                h.charges, 
                h.pymts, 
                h.adj, 
                h.drg, 
                h.mcare_reimb 
         FROM   web5.dbpde.dbo.cm_tblcontractanalysis_head AS h 
                INNER JOIN #troubleaccts2 AS r 
                  ON r.run_id = h.run_id 
                     AND r.headid = h.headid) 
SELECT   TOP 100 h.rowid, 
                 h.headid, 
                 h.run_id, 
                 h.contractid, 
                 h.acct_no, 
                 h.insurance, 
                 m.ins_name, 
                 h.ptp, 
                 h.patient_tp, 
                 h.admit, 
                 h.discharge, 
                 h.charges, 
                 h.pymts, 
                 h.adj, 
                 h.drg, 
                 d.mcare_reimb, 
                 d.dept_code, 
                 d.proc_code, 
                 d.cpt_code, 
                 d.rev_code, 
                 d.trans_dt, 
                 d.qty, 
                 d.rev, 
                 d.reimb_method, 
                 d.qty * d.reimb_amt AS reimb_amt, 
                 d.reimb_match, 
                 d.reimb_percent, 
                 d.reimb_notes 
FROM     web5.dbpde.dbo.cm_tblcontractanalysis_detail AS d 
         INNER JOIN head AS h 
           ON d.run_id = h.run_id 
              AND d.headid = h.headid 
         INNER JOIN dbo.tblmap_ins AS m 
           ON h.insurance = m.insurance 
ORDER BY h.rowid, 
         d.trans_dt, 
         Abs(d.mcare_reimb - (d.qty * d.reimb_amt)) DESC

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.