December 20, 2004 at 9:21 am
I've posted before on this issue, but I'm almost finished now and just need help to finish it off. Here's my statement;
SELECT Div_Cat, Contract, [MNAllowable per TRVU], MAX(cnt) AS mntrvu_mode
FROM (
SELECT Div_Cat, Contract, [MNAllowable per TRVU], count(*) AS cnt
FROM [2004 Single CPT Inv Summary db]p
WHERE [MNAllowable per TRVU] is not null and Contract != 'N/A'
GROUP BY Div_Cat, Contract, [MNAllowable per TRVU]
) AS A
GROUP BY Div_Cat, Contract, [MNAllowable per TRVU]
This is used to figure out the Mode of the [MNAllowable per TRVU]. The result is Div_Cat, Contract, MNAllowable per TRVU, mntrvu_mode. The results are correct however it is listing all Modes. I just need to pair this view down to only the Max of the mntrvu_mode not every one. For example, this current view will result in;
Specialty,Commercial,55,50
Specialty,Commercial,45,10
What I really need is only the first line as the mntrvu_mode has 50 which mean it is the true mode. Any help would be greatly appreciated.
Cheers,
Max
December 20, 2004 at 9:59 am
SELECT Div_Cat, Contract, [MNAllowable per TRVU], cnt AS mntrvu_mode
FROM (
SELECT Div_Cat, Contract, [MNAllowable per TRVU], count(*) AS cnt
FROM [2004 Single CPT Inv Summary db]p
WHERE [MNAllowable per TRVU] is not null and Contract != 'N/A'
GROUP BY Div_Cat, Contract, [MNAllowable per TRVU]
) AS A
where cnt=(select max(cnt) from (
SELECT Div_Cat, Contract, [MNAllowable per TRVU], count(*) AS cnt
FROM [2004 Single CPT Inv Summary db]p
WHERE [MNAllowable per TRVU] is not null and Contract != 'N/A'
GROUP BY Div_Cat, Contract, [MNAllowable per TRVU]
) AS B)
December 20, 2004 at 10:09 am
Osaba thanks for the response. However, what I failed to mention is that there are about 20 different Div_Cat and 3 Contracts. I need the Mode for each of the Div_Cats and accompanying Contract, so it might look like this in the end;
Primary,ContractA,55,28
Primary,ContractB,48,32
Primary,ContractC,56,40
Specialty,ContractA,25,50
Specialty,ContracB,35,49
Specialty,ContractC,40,28
Hopefully this is a little clearer.
Cheers!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply