Audit拒否されたクレームの数千のレコードを含む単一の大きなテーブルがあります。それぞれがサプライヤーからのものであり、拒否の理由とステータスがあります。だけidがユニークです。
id | denial | supplier | status
---|------------------|---------------|---------
1 | Duplicate claim | ACME | Adjusted
2 | Not authorized | Umbrella Corp | Adjusted
3 | Not authorized | Stark Ind. | Adjusted
4 | Rec'd after due | ACME | Override
5 | Duplicate claim | Stark Ind. | Adjusted
... etc
私が必要としているのは、レコード数による「調整済み」ステータスクレームの上位5つの拒否理由と、それらの拒否理由のそれぞれに対するレコード数による上位プロバイダーです。
denial | cnt_denial | top_supplier | cnt_top_supplier
----------------|------------|---------------|-----------------
Not authorized | 917 | Stark Ind. | 351
Duplicate claim | 685 | Stark Ind. | 195
Not in contract | 525 | ACME | 216
Rec'd after due | 512 | Umbrella Corp | 500
Explosions | 349 | ACME | 231
私は多くの方法を試しましたが、主に他のやや類似したソリューションを適応させようとしましたが、ここではわずかなSQLの知識を超えてしまい、イライラしています。私が試したいくつかの解決策は、MS Access（2010）では機能しません。最初に2つのクエリを作成しましたが、必要な方法でそれらを結合するのに問題があります。
このクエリは、必要な拒否理由データを正確に返します。
SELECT TOP 5 denial, Count(*) AS cnt_denial
FROM Audit
GROUP BY status, denial
HAVING status="Adjusted"
ORDER BY Count(*) DESC;
そして、このクエリには、テーブル内のすべての拒否/サプライヤグループのカウントがあります。拒否の理由ごとにトップサプライヤーを取得する方法がわかりません。簡単なはずですが、問題が発生しています。編集：これは私の主な問題です。拒否の理由ごとにトップの単一サプライヤーを獲得できれば、参加を理解できます。MAXを使ってみましたが、まだ成功していません。/編集
SELECT denial, supplier, Count(*) AS cnt_supplier
FROM Audit
GROUP BY denial, supplier, status
HAVING status="Adjusted"
ORDER BY Count(*) DESC;
これをExcelからADO経由で渡すだけなので、単一のクエリが必要です。どんな助けでも大歓迎です。
これが完全に有効なAccessSQLであるかどうかはわかりません。そうでない場合は、それほど調整する必要はありません。
select
d.denial,
d.cnt_denial,
ds.supplier as top_supplier,
ds.cnt_supplier as cnt_top_supplier
from
(
select top 5 denial, count(*) as cnt_denial
from Audit
where status = 'Adjusted'
group by denial
order by count(*) desc
) d
inner join
(
select denial, supplier, count(*) as cnt_supplier
from Audit
where status = 'Adjusted'
group by denial, supplier
) ds
on ds.denial = d.denial
where not exists (
select 1
from Audit as a2
where a2.status = 'Adjusted'
and a2.denial = ds.denial and a2.supplier <> ds.supplier
group by a2.supplier
having count(*) > ds.cnt_supplier
/* or count(*) = ds.cnt_supplier and a2.supplier < ds.supplier -- tiebreaker */
)
order by d.cnt_denial desc, ds.supplier
それを使用するtop 5ことにより、タイに関係なく5行のみを取得します。次に、グループごとの上位1つがサブクエリで処理されます。その場合、タイを含めて、最終結果で5行を超える可能性があります。必要に応じて拒否レベルで同点を含めること、または同点を解除してサプライヤランキングから除外することはそれほど難しくありません。
編集：私はいくつかのテストデータをまとめましたが、タイブレイクはSQLServerで機能しているようです。http://rextester.com/ZEWJ43486
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加