Top(50) Per Provider 1

[mholbert]

I am working on a sql statement to collect the top 50 highest patient balances per provider. I am running against a MSSQL2005 db.

At present I have the following, which returns the top 50 for the whole database, not per provider, how do I change this to collect the 50 highest balances per provider?

Any help is appreciated.

MH

select sum(c.pat_amt) as PatBal,
replace(ltrim(replace(pat.med_rec_nbr, '0', ' ')), ' ', '0') as Chart#,
lp.desc_30 as Provider
into #PatBal
from charges c
inner join lrdc_providers lp on lp.provider_id = c.rendering_id
inner join patient pat on pat.person_id = c.person_id
inner join person per on per.person_id = pat.person_id
where c.pat_amt > 0.00
group by lp.desc_30,pat.med_rec_nbr
order by sum(c.pat_amt) desc

select top(50) PatBal,chart#,provider
from #PatBal
group by provider,chart#

 

[markros]

;with cte_ProvidersSummary as (select    sum(c.pat_amt) as PatBal,    replace(ltrim(replace(pat.med_rec_nbr, '0', ' ')), ' ', '0') as Chart#,    lp.desc_30 as Provider from    charges c
inner    join lrdc_providers lp on lp.provider_id = c.rendering_id
inner    join patient pat on pat.person_id = c.person_id
inner    join person per on per.person_id = pat.person_id
where    c.pat_amt > 0.00
group by lp.desc_30,pat.med_rec_nb)

select * from (select *, row_number() over (partition by Provider order by PatBal DESC) as rowNum from cte_ProviderSummary) where RowNum <=50

 

[mholbert]

its gonna take me a bit to make sense out of how that works, but in the meantime, i am getting the following syntax error.

Msg 156, Level 15, State 1, Occurred on Line: 1
Incorrect syntax near the keyword 'where'.

I copied and pasted what you sent and then had to clean it up bc the copy/paste merged stuff a bit and lost the spacing between certain words.

What am I missing?

MH

 

[markros]

Sorry, I guess it was late last night when I wrote it - I don't know how else to explain the typo I made

select * from (select *, row_number() over (partition by Provider order by PatBal DESC) as rowNum from cte_ProviderSummary) X where RowNum <=50

I forgot to alias the derived table.

 

[bborissov]

markros,
why you use CTE here?
The same thing could be done wit a simple derived table with ROW_NUMBER() in it?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

May be because the initial select was too complex to me and besides we want to partition by SUM and I hesitated to include this into row_number function.

I think this way is simpler to understand.

 

[bborissov]

I don't know why I hate CTEs so much.
Sometimes, they are mandatory, but I prefer to stay away from them when it is possible .

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

We're in disagreement here I personally love them.

 

[bborissov]

It's obvious

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mholbert]

Awesome, I appreciate the help. Clearly i have lots to learn about t-sql. I would be interested to see how you would make the initial query less complex, and how it could be done with a derived table with row number in it.

Thanks, MH

 

[bborissov]

[COLOR=blue]SELECT[/color] PatBal, chart#, provider
[COLOR=blue]FROM[/color] ([COLOR=blue]SELECT[/color] *, ROW_NUMBER() [COLOR=blue]OVER[/color] ([COLOR=#FF00FF]PARTITION[/color] [COLOR=blue]BY[/color] Provider [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] PatBal [COLOR=#FF00FF]DESC[/color]) [COLOR=blue]AS[/color] [COLOR=#FF00FF]RowNum[/color]
             [COLOR=blue]FROM[/color]([COLOR=blue]SELECT[/color] SUM(c.pat_amt)                                               [COLOR=blue]AS[/color] PatBal,
                         [COLOR=#FF00FF]REPLACE[/color]([COLOR=#FF00FF]LTRIM[/color]([COLOR=#FF00FF]REPLACE[/color](pat.med_rec_nbr, [COLOR=red]'0'[/color], [COLOR=red]' '[/color])), [COLOR=red]' '[/color], [COLOR=red]'0'[/color]) [COLOR=blue]AS[/color] Chart#,
                         lp.desc_30                                                   [COLOR=blue]AS[/color] Provider
                  [COLOR=blue]FROM[/color] charges c
                  [COLOR=blue]inner[/color] [COLOR=blue]join[/color] lrdc_providers lp [COLOR=blue]on[/color] lp.provider_id = c.rendering_id
                  [COLOR=blue]inner[/color] [COLOR=blue]join[/color] patient pat [COLOR=blue]on[/color] pat.person_id = c.person_id
                  [COLOR=blue]where[/color]  c.pat_amt > 0.00 
                  [COLOR=blue]group[/color] [COLOR=blue]by[/color] lp.desc_30,
                          pat.med_rec_nbr) Tbl1) Tbl2
[COLOR=blue]WHERE[/color] [COLOR=#FF00FF]RowNum[/color] < 51

NOT TESTED!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mholbert]

interesting,is there a performance gain with your logic versus where i started?

MH

 

[markros]

Nope - it's the exactly same thing Only pretty colored

Keep what you currently have.

 

[bborissov]

Test it
There is an "Include Actual Execution plan" button in SSMS

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mholbert]

If either of you care to expound on your statements, I would love some plain english explanations of your syntax.

What is CTE? it looks like you are defining it on the fly using the 'with ctexxx as'. are you using a variable as a table?

What is the Over statement?

I need to look up Partition, it appears to provide a higher level of grouping than group by.

Reading all of this and trying to make it work makes we want to get a tsql book and force myself to learn it better.

I have to write a stored procedure next week that I believe will require a fetch in it. Im sure i will post more questions when i get half way through that and get stuck.

Clearly both of you are well versed in sql, I much appreciate your assistance. What you spit out in minutes would have taken me hours to figure out, if i could have figured it out at all.

Thanks Again! I hope everyone has a Great Thanksgiving!

MH

 

[markros]

Hi,

Perhaps you may find this link to be of help

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/cte-and-hierarchical-queries

This link contains links to several other interesting links involving CTE and a CTE (recursive) definition from BOL.

Basically, you can consider them as a table created in memory and available to use in the next statement following it.

I use CTE (Common Table Expressions) primarily for readability purpose - in many cases you can use derived tables with the exact same result (as Boris demonstrated). However, the recursive CTE is a unique situation, and the only alternative solutions (in 98% of these cases) for such problems would be cursor based solutions.