医療機関番号，区分点数コードから全国の医療機関コードを生成する

　先の投稿では医科と歯科のファイルの分類が誤っていた．今回，正しく分類し直したので再度投稿する．

医科のファイルは「医科」および「歯科併設」，歯科のファイルは「歯科」および「医科併設」

　先の投稿では医科のファイルを「医科」および「医科併設」，歯科のファイルを「歯科」および「歯科併設」と分類していたが，誤りであった．

　正しく分類して各厚生局ごと，医科と歯科ごとにフォルダに分けた．Power Queryでそれぞれのフォルダからインポートし，さらに医科ごと，歯科ごとに一つずつのファイルにインポートした．詳細は全国の医療機関コードを取得するを参照のこと．

M_DentalInstitution

M_MedicalInstitution

SQL Serverにインポート

　ウィザードを使用してSQL Serverにインポートした．列マッピングは下図を参照のこと．

医療機関コード，都道府県の列の作成

　M_DentalInstitutionテーブルを例に説明する．テーブルを右クリックして「デザイン」を選択する．

　「医療機関コード」と「都道府県」列を作成する．データ型は下図を参照のこと．

郵便番号テーブルと事業所の個別郵便番号テーブルから都道府県と地方公共団体コードを取得し，医療機関コードを生成する

　下記クエリを実行して郵便番号テーブルと事業所の個別郵便番号テーブルから都道府県と地方公共団体コードを取得し，区分点数コード，医療機関番号と合わせて10桁の医療機関コードを生成する．

UPDATE  M_DentalInstitution
--UPDATE	M_MedicalInstitution
SET		医療機関コード = CASE WHEN LEFT(Z.全国地方公共団体コード, 2) + [区分点数コード] +[医療機関番号] IS NULL 
        THEN LEFT(J.所在地のJISコード, 2) + [区分点数コード] +[医療機関番号]
        ELSE LEFT(Z.全国地方公共団体コード, 2) + [区分点数コード] +[医療機関番号] END
    ,   都道府県 = CASE WHEN Z.都道府県名 IS NULL THEN J.都道府県名 ELSE Z.都道府県名 END
FROM    dbo.M_DentalInstitution AS M
--FROM	dbo.M_MedicalInstitution	AS M
LEFT	JOIN	dbo.M_ZIPCODE	AS Z
ON		M.郵便番号 = Z.郵便番号
LEFT	JOIN	dbo.M_JIGYOSYO	AS J
ON		M.郵便番号 = J.大口事業所個別番号

(63582 行に影響しました)

　さらに次のクエリを実行して残りのレコードを更新する．

WITH    UNKNOWNZIP
AS
(
SELECT CASE WHEN LEFT(Z.全国地方公共団体コード, 2) + [区分点数コード] +[医療機関番号] IS NULL 
       THEN LEFT(J.所在地のJISコード, 2) + [区分点数コード] +[医療機関番号]
       ELSE LEFT(Z.全国地方公共団体コード, 2) + [区分点数コード] +[医療機関番号] END  AS '医療機関コード'
       ,M.区分点数コード
       ,M.医療機関番号
       ,[医療機関名]
       ,M.[郵便番号]
       ,Z.都道府県名
       ,M.所在地
--  FROM  dbo.M_MedicalInstitution    AS M
  FROM [dbo].[M_DentalInstitution] AS M
  LEFT  JOIN    dbo.M_ZIPCODE   AS Z
  ON    M.郵便番号 = Z.郵便番号
  LEFT  JOIN    dbo.M_JIGYOSYO  AS J
  ON    M.郵便番号 = J.大口事業所個別番号
  WHERE CASE WHEN LEFT(Z.全国地方公共団体コード, 2) + [区分点数コード] +[医療機関番号] IS NULL 
       THEN LEFT(J.所在地のJISコード, 2) + [区分点数コード] +[医療機関番号]
       ELSE LEFT(Z.全国地方公共団体コード, 2) + [区分点数コード] +[医療機関番号] END IS NULL
), 
CTE(ZIPCODE, PREF, LGCODE)
AS
(
SELECT  UNKNOWNZIP.郵便番号
    ,   MAX(Z.都道府県名)
    ,   LEFT(MAX(Z.全国地方公共団体コード), 2)
FROM    dbo.M_ZIPCODE   AS Z
INNER   JOIN    UNKNOWNZIP
ON      LEFT(Z.郵便番号, 3) = LEFT(UNKNOWNZIP.郵便番号, 3)
GROUP   BY  LEFT(Z.郵便番号, 3), UNKNOWNZIP.郵便番号
)
UPDATE  M
SET     医療機関コード = CTE.LGCODE + M.区分点数コード + M.医療機関番号
    ,   都道府県 = CTE.PREF
--FROM    dbo.M_MedicalInstitution AS M
FROM    dbo.M_DentalInstitution AS M
INNER   JOIN    CTE
ON  M.郵便番号 = CTE.ZIPCODE

(9 行に影響しました)

　医科の場合，行数はまた変化する．

(93144 行に影響しました)

(39 行に影響しました)

重複しているレコードを抽出し削除する

　下記のクエリを実行して重複しているレコードを抽出する．

SELECT	M.医療機関コード
	,	COUNT(*)
	,	MAX(M.医療機関名)
	,	MIN(M.医療機関名)
FROM	dbo.M_DentalInstitution AS M
GROUP	BY M.医療機関コード
HAVING	COUNT(*) > 1

(44 行に影響しました)

1130404467	2	あやクリニック　歯科・皮ふ科	あやクリニック　歯科・皮ふ科
1130602631	2	グレースデンタルメディカルクリニック　埼玉東分院	グレースデンタルメディカルクリニック　埼玉東分院
0435135447	2	国家公務員共済組合連合会東北公済病院　仙台合同庁舎診療所	国家公務員共済組合連合会東北公済病院　仙台合同庁舎診療所
0435430566	2	仙台市秋保診療所	仙台市秋保診療所
1433030392	2	セントゼビアクリニック	セントゼビアクリニック
1433640885	2	山手台クリニック	山手台クリニック
1439803206	2	三浦しらとり園診療所	三浦しらとり園診療所
0830630968	2	原田医院歯科内科	原田医院歯科内科
0834430886	2	医療法人社団洋光会　協和ガーデンクリニック	医療法人社団洋光会　協和ガーデンクリニック
1430601732	2	保土ケ谷クリニック	保土ケ谷クリニック
1432630952	2	アイリスデンタル・メディカル	アイリスデンタル・メディカル
1433030236	2	セントルカ眼科歯科クリニック	セントルカ眼科歯科クリニック
1435030523	2	川崎駅Ｄ＆Ｄクリニック歯科皮膚科	川崎駅Ｄ＆Ｄクリニック歯科皮膚科
0435135231	2	セルプロケアデンタルクリニック漢方内科	セルプロケアデンタルクリニック漢方内科
1130404368	2	グレースデンタルメディカルクリニック　埼玉分院	グレースデンタルメディカルクリニック　埼玉分院
1432130474	2	グレースデンタルメディカルクリニック　鎌倉分院	グレースデンタルメディカルクリニック　鎌倉分院
1432330454	2	グレースデンタルメディカルクリニック小田原分院	グレースデンタルメディカルクリニック小田原分院
1434200291	2	さつかわ医院	さつかわ医院
1537131086	2	上所歯科耳鼻科	上所歯科耳鼻科
1231031194	2	医療法人社団史祥会　房総メディカルクリニック	医療法人社団史祥会　房総メディカルクリニック
1232136216	2	柏五味歯科内科クリニック	柏五味歯科内科クリニック
1232331668	2	グレースデンタルメディカルクリニック　千葉分院	グレースデンタルメディカルクリニック　千葉分院
1431930361	2	神奈川歯科大学附属病院	神奈川歯科大学附属病院
1432230373	2	グレースデンタルメディカルクリニック　湘南分院	グレースデンタルメディカルクリニック　湘南分院
1433330289	2	グレースデンタルメディカルクリニック　横浜分院	グレースデンタルメディカルクリニック　横浜分院
1433830395	2	コンパス内科歯科クリニック都筑センター南	コンパス内科歯科クリニック都筑センター南
1132901056	2	伊勢医科歯科クリニック	伊勢医科歯科クリニック
1136530786	2	グレースデンタルメディカルクリニック　大宮分院	グレースデンタルメディカルクリニック　大宮分院
1430342048	2	誠敬会クリニック	誠敬会クリニック
1430342121	2	石川医院	石川医院
1432342699	2	町田医科歯科診療所	町田医科歯科診療所
1432647394	2	医療法人社団裕人会　ミナミクリニック	医療法人社団裕人会　ミナミクリニック
0832131288	2	三友歯科耳鼻咽喉科	三友歯科耳鼻咽喉科
1133101342	2	医療法人　栄宏会　２８ＣｌｉｎｉＣ野上歯科医院	医療法人　栄宏会　２８ＣｌｉｎｉＣ野上歯科医院
1136530141	2	シンポ歯科クリニック浦和	シンポ歯科クリニック浦和
1430101691	2	鶴見大学歯学部附属病院	鶴見大学歯学部附属病院
1432230001	2	コンパス内科歯科クリニック藤沢湘南台	コンパス内科歯科クリニック藤沢湘南台
1435043211	2	小林整形外科歯科医院	小林整形外科歯科医院
1530131224	2	日本歯科大学新潟病院	日本歯科大学新潟病院
1136503742	2	吉見医科歯科クリニック	吉見医科歯科クリニック
1136509657	2	ティーエム歯科医院	ティーエム歯科医院
1234730487	2	グレースデンタルメディカルクリニック　北総分院	グレースデンタルメディカルクリニック　北総分院
1430243352	2	神奈川歯科大学附属横浜クリニック	神奈川歯科大学附属横浜クリニック
1431043314	2	医療法人社団　木原会　木原歯科・内科医院	医療法人社団　木原会　木原歯科・内科医院

　見たところ，完全に重複しているように見える．この場合，重複レコードの削除が必要である．念の為完全に重複しているか確認してみる．

SELECT	M.医療機関コード
	,	COUNT(*)
	,	MAX(M.医療機関名)
	,	MIN(M.医療機関名)
FROM	dbo.M_DentalInstitution AS M
GROUP	BY M.医療機関コード
HAVING	COUNT(*) > 1
AND	MAX(M.医療機関名) > MIN(M.医療機関名)

(0 行に影響しました)

　完全に重複しているようである．余談であるが，もし上記のクエリでなにかが抽出されてきたら，コード体系が破綻していることを意味する．地方厚生局に通報しなければならない案件である．

　では次のクエリを実行して重複しているレコードを削除しよう．

WITH CTE AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY M.医療機関コード ORDER BY (SELECT 0)) AS rn
  FROM
    --dbo.M_MedicalInstitution    AS M
    dbo.M_DentalInstitution AS M
)
DELETE 
--SELECT  *
FROM
  CTE
WHERE
  rn > 1

(44 行に影響しました)

　医科では次のようである．

(569 行に影響しました)

まとめ

　全国の地方厚生局から医科，歯科のファイルをダウンロードして，地方公共団体コード，区分点数コードおよび医療機関番号から医療機関コードを生成した．完全な重複以外，コード体系に致命的な重複は認められなかった．

　なお，ダウンロードしたファイルは2025年9月1日時点のものである．