Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Select Top

  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Select Top

    SQL Question.

    I am pulling top 5 denial reasons for providers (doctors).

    I need to set this up for roughly 35 unique doctors on a monthly basis -- with more to be added over time.

    Below is the sql to perform this for a single provider.

    What I'm stuck on is how to use the SELECT TOP 5 across a set of providers (Each one has their top 5 denial reasons).

    I appreciate any help.


    My current query.

    DECLARE @PERIODID AS INT
    SET @PeriodID = 3200410
    SELECT TOP 5 l.PeriodID, p.ProviderName, p.AltProviderID_1,
    e.EOPCode, ISDenial,
    SUM(DeniedAmount) AS DeniedAmount
    FROM dvOperationalLine l
    INNER JOIN dimProvider p
    ON l.ProviderID = p.ProviderID
    INNER JOIN dimEOP e
    ON e.EOPCodeID = l.EOPCodeID
    WHERE l.PeriodID = @PeriodID
    AND ISDenial = 1
    AND (p.AltProviderID_1 = '0007049'
    )
    GROUP BY l.PeriodID, IsDenial, p.ProviderName,
    p.AltProviderID_1, e.EOPCode
    ORDER BY DeniedAmount DESC;

  2. #2
    Join Date
    Feb 2004
    Posts
    78
    I the easiest method would be to create a cursor and loop through for each provider and insert into a temp/variable table. Then select from this table.

    Something like the following (I just whipped this up so..)

    declare @provider varchar(16), @status int
    declare @outputtable (column1 int, etc..)

    declare C cursor LOCAL for select provider_id from provider_list -- (your list ov providers)

    open C fetch next from C into @provider
    set @status=@@fetch_status

    while @status = 0

    begin
    insert into @outputtable
    SELECT TOP 5 l.PeriodID, p.ProviderName, p.AltProviderID_1,
    e.EOPCode, ISDenial,
    SUM(DeniedAmount) AS DeniedAmount
    FROM dvOperationalLine l
    INNER JOIN dimProvider p
    ON l.ProviderID = p.ProviderID
    INNER JOIN dimEOP e
    ON e.EOPCodeID = l.EOPCodeID
    WHERE l.PeriodID = @PeriodID
    AND ISDenial = 1
    AND (p.AltProviderID_1 = @provider
    )
    GROUP BY l.PeriodID, IsDenial, p.ProviderName,
    p.AltProviderID_1, e.EOPCode
    ORDER BY DeniedAmount DESC

    fetch next from C into @provider
    set @status=@@fetch_status
    end

    close C
    deallocate C
    select * from @outputtable

    Hope this helps,
    Eric

  3. #3
    Join Date
    Jun 2004
    Posts
    57

    Thanks

    I'm a bit of a beginner, so this was my first cursor. A cool way to tackle this kind of problem. Thanks for opening a new door.

    Ray

  4. #4
    Join Date
    Feb 2004
    Posts
    78
    Glad I could help! Cursors are really great once you get the hang of them.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You ain't from around these parts, are you stranger? On this side o' the river, we hates cursors with a passion, and hates greenhorns what uses 'em even more. Cursors are for VB developer wimps, and they suck resources nigh unto like a praire twister.

    Code:
    DECLARE	@PERIODID AS INT 
    
    SET	@PeriodID = 3200410 
    
    
    insert into @outputtable
    	(PeriodID,
    	ProviderName,
    	ALtProviderID_1,
    	dimEOP.EOPCode,
    	ISDenial,
    	DeniedAmount)
    SELECT	dvOperationalLine.PeriodID,
    	dimProvider.ProviderName,
    	dimProvider.AltProviderID_1, 
    	ISDenial, 
    	SUM(DeniedAmount) AS DeniedAmount 
    FROM	dvOperationalLine 
    	INNER JOIN dimProvider ON dvOperationalLine.ProviderID = dimProvider.ProviderID 
    	INNER JOIN dimEOP ON dimEOP.EOPCodeID = dvOperationalLine.EOPCodeID 
    WHERE	dvOperationalLine.PeriodID = @PeriodID 
    	AND ISDenial = 1 
    	AND dimProvider.AltProviderID_1 = '0007049' 
    GROUP BY dvOperationalLine.PeriodID,
    	dimProvider.ProviderName,
    	dimProvider.AltProviderID_1, 
    	dimEOP.EOPCode,
    	ISDenial
    
    select	distinct
    	output1.PeriodID,
    	output1.ProviderName,
    	output1.ALtProviderID_1,
    	output1.EOPCode,
    	output1.ISDenial,
    	output1.DeniedAmount
    from	@outputtable output1
    	inner join @outputtable output2
    		on output1.PeriodID = output2.PeriodID
    		and output1.ProviderName = output2.ProviderName
    		and output1.ALtProviderID_1 = output2.ALtProviderID_1
    		and output1.EOPCode = output2.EOPCode
    		and output1.ISDenial = output2.ISDenial
    		and output1.DeniedAmount >= output2.DeniedAmount
    having count(*) <= 5
    Note that this entire operation could be performed as a single SELECT statement without the use of a tempory table, but I have split it out to illustrate the method. Also, some relationships are unnecessary (ISDenial is always 1 in the temporary table), but again they have been left in to illustrate the method.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dude, you are just outta control today...

    WStill bucking the alias thing...hmmmmmmmmm
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I have come to the conclusion that is now unpatriotic not to code with an arrogant swagger....

    ...but mah' six-gun appears to have misfired on that one. Here is the code cleaned of superfluous columns:

    Code:
    insert into @outputtable
    	(PeriodID,
    	ProviderName,
    	dimEOP.EOPCode,
    	DeniedAmount)
    SELECT	dvOperationalLine.PeriodID,
    	dimProvider.ProviderName,
    	SUM(DeniedAmount) AS DeniedAmount 
    FROM	dvOperationalLine 
    	INNER JOIN dimProvider ON dvOperationalLine.ProviderID = dimProvider.ProviderID 
    	INNER JOIN dimEOP ON dimEOP.EOPCodeID = dvOperationalLine.EOPCodeID 
    WHERE	dvOperationalLine.PeriodID = @PeriodID 
    	AND ISDenial = 1 
    	AND dimProvider.AltProviderID_1 = '0007049' 
    GROUP BY dvOperationalLine.PeriodID,
    	dimProvider.ProviderName,
    	dimEOP.EOPCode
    
    select	distinct
    	output1.PeriodID,
    	output1.ProviderName,
    	output1.EOPCode,
    	output1.DeniedAmount
    from	@outputtable output1
    	inner join @outputtable output2
    		on output1.PeriodID = output2.PeriodID
    		and output1.ProviderName = output2.ProviderName
    		and output1.EOPCode = output2.EOPCode
    		and output1.DeniedAmount >= output2.DeniedAmount
    having count(*) <= 5
    ...note that you will need to COMMENT OUT from the JOIN clause the fields that designate the groups within which you want to get your top 5 records.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Posts
    78

    Talking

    That is true, sir! I agree that in general, cursors should only be used when a better alternative is not out there.

    In this instance, you've clearly shown there is a better alternative.

    I'd challenge you to an execution plan dual, your query 'gainst mine, high noon, ... but i'm too lazy. And I'm not a real dba, so I won't win. :-)

    Eric
    Last edited by Dilyias; 12-21-04 at 19:00.

  9. #9
    Join Date
    Jun 2004
    Posts
    57
    Blindman.
    Thanks. I setup and ran the query (I ditched the cursor) and it gave me all 13 EOPCode reasons for the provider 0007049. I'm a bit new with SQL and I wasn't sure how to interpret your instruction
    'note that you will need to COMMENT OUT from the JOIN clause the fields that designate the groups within which you want to get your top 5 records.'
    I need to do something to limit the result set to just the top 5 EOPcodes for each provider (in this case I only used one 0007049) by totalDeniedAmount (each code has a total denied for that code and I want the top five.

    A second question. Is there a way to add a field that tags each code Top1, Top2, Top3, Top4, and Top5 for each provider (each 0007049)

    I appreciate any help you can offer.

    Ray

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If you're dealing with denial codes for claims submitted by those providers, then TOP 5 may not necessarily cut it for your org to avoid DoF complaints for your insurance/HMO. Ideally you would have to provide ALL denial codes (descriptions in foot notes or in the legend page) on your remittance advice report, - otherwise your CEO might run into legal issues and...fire the DBA!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    yeah
    what he said.

    pardner.

  12. #12
    Join Date
    Jun 2004
    Posts
    57
    Funny you should say that. The problem is that we have lots of denial reports with every denial reason and our provider relations folks, on feedback from execs and providers, wanted to try a top 5. Go figure.

    Indeed though, the dba often goes under the bus when the previously 'unknown' claims issue (turnaround, denials...) goes on the radar.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try this as your second query:

    Code:
    select	distinct
    	output1.ProviderName,
    	output1.EOPCode,
    	output1.DeniedAmount,
    	'Top' + count(*) as TopTag
    from	@outputtable output1
    	inner join @outputtable output2
    		on output1.ProviderName = output2.ProviderName
    		and output1.EOPCode = output2.EOPCode
    		and output1.DeniedAmount <= output2.DeniedAmount
    group by 	output1.ProviderName,
    	output1.EOPCode,
    	output1.DeniedAmount
    having count(*) <= 5
    Removed join on PeriodID, as you do not want to group by that field.
    Addes Group By clause (forgot on previous code).
    Changed DeniedAmount comparison to <= (I think this is what you want).
    Added TopTag field.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jun 2004
    Posts
    57
    Blindman.
    Ran it. something wrong with the: 'Top' + count(*) as TopTag.

    Gave me this: Server: Msg 245, Level 16, State 1, Line 32
    Syntax error converting the varchar value 'Top' to a column of data type int.

    I assume it thinks the count(*) means TopTag is an int, but with the 'Top' concatenation it needs to be a VARCHAR.

    Suggestion appreciated.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry:

    'Top' + cast(count(*) as varchar(10)) as TopTag
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •