August 27, 2008 at 3:13 pm
Ok, I have the following resultset, and I want to return only one line per claim. I want to know the claimid and revcode for each claim, returning ONLY the revcode with the most units OR if multiple revcodes have the same units, the higher revcode.
Every way I try to do this using MAX or GROUP BY, I get two results for the claims with multiples.
As I look at the output from the below, I can SEE what I want, the top line for each claim. I just can't figure out how the heck to pull just that line.
Here's sample data.
IF object_id('TempDB..#myHead') IS NOT NULL BEGIN DROP TABLE #myHead END
CREATE TABLE #myHead (ID int identity(1,1),
claimid char(3),
servunits int,
revcode char(3))
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('034',3,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('098',3,'174')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('098',1,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('034',3,'172')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('126',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('134',2,'172')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('190',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('195',3,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('235',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('237',3,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('255',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('281',1,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('283',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('295',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('306',3,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('334',4,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('390',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('428',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('475',1,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('483',6,'174')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('522',3,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('524',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('542',3,'173')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('542',3,'174')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('553',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('555',3,'172')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('613',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('632',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('635',3,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('650',1,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('675',1,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('847',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('876',1,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('892',2,'171')
INSERT INTO #myHead (claimid,servunits,revcode) VALUES ('896',2,'171')
-- I want the claimid and revcode where the most units exist of all the revcodes,
-- OR if the revcodes have matching units,
-- the highest rev code
-- (basically the first line for each claimid in the below)
-- claim 034 should give me revcode 172, claim 098 should give me revcode 174
SELECT claimid, servunits, revcode
FROM #myHead
ORDER BY claimid, servunits desc, revcode desc
Thanks in advance - Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 27, 2008 at 5:00 pm
I want to return only one line per claim. I want to know the claimid and revcode for each claim,
Try this as your select statement
SELECT claimid, Max(revcode) AS 'RevCode'
FROM #myHead
GROUP BY claimid
ORDER BY claimid desc
Giving this extract from all the results
claimid RevCode
------- -------
255 171
237 171
235 171
195 171
190 171
134 172
126 171
098 174
034 172
And let me say thanks for your excellent presentation of the task, that is table statement and sample data in a format easy for some one who wants to help to use.
August 27, 2008 at 5:26 pm
This might be your solution:
SELECT H.claimid, H.servunits, MAX(H.revcode) as revcode
FROM #myHead H
INNER JOIN (
SELECT claimid, MAX(servunits) servunits
FROM #myHead
GROUP BY claimid
) DT ON DT.claimid = H.claimid and DT.servunits = H.servunits
GROUP BY H.claimid, H.servunits
ORDER BY H.claimid, H.servunits desc
Derived table here selects max servunits for each claim. By joining #myHead to this table you remove all records where servunits is not max for each claimid.
After that you just select max revcode for each of remaining (claimid, servunit) groups.
_____________
Code for TallyGenerator
August 27, 2008 at 5:39 pm
bitbucket (8/27/2008)
I want to return only one line per claim. I want to know the claimid and revcode for each claim,
Try this as your select statement
SELECT claimid, Max(revcode) AS 'RevCode'
FROM #myHead
GROUP BY claimid
ORDER BY claimid desc
Giving this extract from all the results
claimid RevCode
------- -------
255 171
237 171
235 171
195 171
190 171
134 172
126 171
098 174
034 172
And let me say thanks for your excellent presentation of the task, that is table statement and sample data in a format easy for some one who wants to help to use.
Thanks bitbucket, I appreciate the help, but that's actually only part of what I'm looking for, the other half being the revcode with the most units. I think Sergiy's got it, it hit me on the way home from work that I was trying to combine two steps into one, and needed to just separate the two conditions.
I try to learn from all of your (collective 'your' here) experience and suggestions, glad to see that it's helpful.
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 27, 2008 at 5:46 pm
Sergiy (8/27/2008)
This might be your solution:
SELECT H.claimid, H.servunits, MAX(H.revcode) as revcode
FROM #myHead H
INNER JOIN (
SELECT claimid, MAX(servunits) servunits
FROM #myHead
GROUP BY claimid
) DT ON DT.claimid = H.claimid and DT.servunits = H.servunits
GROUP BY H.claimid, H.servunits
ORDER BY H.claimid, H.servunits desc
Derived table here selects max servunits for each claim. By joining #myHead to this table you remove all records where servunits is not max for each claimid.
After that you just select max revcode for each of remaining (claimid, servunit) groups.
Thanks much, Sergiy! Looks like that will work.
I appreciate the fast response. This is part of the logic that will help me to automate a process that currently needs to be touched by a claim processor around 1,000 times a day, so will have major impact on our daily business. I'll be sure to give credit and a url in the code. 😀
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 27, 2008 at 6:27 pm
You welcome.
It's easy to give fast response on so well prepared question as yours one. 🙂
Another hint regarding this query:
if it's used much make sure you've got clustered index on (claimid, servunits, revcode). If it's impossible create covering index for the data returned by the query.
_____________
Code for TallyGenerator
August 27, 2008 at 6:46 pm
I agree Sergiy. Even though I was too late, it is a real pleasure to work on a question or problem that is prepared this well and it is really great to see an OP supply without having to be asked. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 27, 2008 at 6:47 pm
Sergiy (8/27/2008)
You welcome.It's easy to give fast response on so well prepared question as yours one. 🙂
Another hint regarding this query:
if it's used much make sure you've got clustered index on (claimid, servunits, revcode). If it's impossible create covering index for the data returned by the query.
Ah, but you're assuming that the users are actually using a query. Silly Sergiy. 😛 No, this is a *manual* process where the user extracts data from the system and enters it into an Excel spreadsheet in order to price a claim, because someone won't buy the software add-on that does this automatically. The Excel version is actually my creation, as a stopgap from a couple years ago before I learned SQL, waiting on a solution from the project team that never materialized. Sad, that creative Excel (not even VBA) can achieve what the multi-billion dollar corporation's production software can't.
I'm now creating it as a script that will just sweep the system daily and pull all of the claims that are ready to be priced, and do it for them, just because they don't think it can be done. (I'm one of those people who likes to be told that, so I have an excuse to do it) It's sort of fun to be the big fish in a very small, murky pond, even if *I* know that I don't know what I don't know. :hehe:
The data will be very temporary, and will only really be hit once for each record.
Thanks for the advice on the indexing though, I'll keep that in mind for the future.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 27, 2008 at 7:00 pm
Why don't you make this query a view and call that view via "Import data -> database query" in Excel?
It will allow users to refresh data instantly with single mouse click.
_____________
Code for TallyGenerator
August 27, 2008 at 8:16 pm
Serigy
That is putting the frosting on the cake, or the cherry on top of the ice cream Sunday. Hope the OP does what you suggest and gets all the accolades due him
August 27, 2008 at 8:23 pm
Sergiy (8/27/2008)
Why don't you make this query a view and call that view via "Import data -> database query" in Excel?It will allow users to refresh data instantly with single mouse click.
Man, I agree with that! You could also make the query autoupdate on "open" and once every minute or two after it's been opened.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2008 at 4:58 am
Great suggestions, but I'm looking to actually update the production db so that rather than just collecting the info, the script will do the work and just populate the correct amount. This was just a piece of the overall script, but I was stuck.
Speaking of queries refreshing on open, though, I have a related question. When I'm running production reports, I don't have the ability to use the scheduler in SQL Server. I tried to set updateable queries in Excel to open using Windows Scheduler, and run overnight, then publish to Sharepoint on Save. (that way I can just give the users one url to go to that won't change)
Works like a charm if I'm logged in, but even though I've entered the username/password in Windows Scheduler, it won't execute for me when I'm logged off.
General question, I know, but don't suppose any of you have any ideas about why that wouldn't work?
Thanks!
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 28, 2008 at 6:46 am
No... not what we're talking about... the spreadsheet would update itself from a view. No scheduling involved.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply