SELECT DISTINCT
[市区町村コード], 
TblCity.市区町村コード + [行政区情報].行政区コード AS [指定都市_行政区コード],
COALESCE([選挙区情報].選挙区コード, '001') AS [選挙区コード],
[選挙人台帳].[投票区コード],
FORMAT([選挙人台帳].[簿冊番号],'000') AS [簿冊番号],
FORMAT([選挙人台帳].[頁番号], '00000') AS [名簿頁番号],
FORMAT([選挙人台帳].[行番号],'00000') AS [名簿行番号],
FORMAT([選挙人台帳].登録日, '0000-00-00') AS [登録基準日コード],
[投票情報].[不在施設番号] AS [施設コード],
CONCAT([選挙人台帳].[投票区コード],
FORMAT([選挙人台帳].[簿冊番号],'000'),
FORMAT([選挙人台帳].[頁番号], '00000'),
FORMAT([選挙人台帳].[行番号],'00000')) AS [名簿番号],
COALESCE([異動情報].[状態区分], '02') AS [状態区分], --異動情報がなければ有権者
[漢字氏名] AS [氏名],
LEFT([漢字氏名], CHARINDEX('　',[漢字氏名]) - 1) AS [氏_日本人],
RIGHT([漢字氏名], LEN([漢字氏名]) - CHARINDEX('　',[漢字氏名])) AS [名_日本人],
[カナ氏名] AS [氏名_フリガナ],
LEFT([カナ氏名], CHARINDEX('　',[カナ氏名]) - 1) AS [氏_日本人_フリガナ],
RIGHT([カナ氏名], LEN([カナ氏名]) - CHARINDEX('　',[カナ氏名])) AS [名_日本人_フリガナ],
[性別], 
(CASE [性別] WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE NULL END) AS [性別表記],
[生年元号リスト].[生年元号] AS [生年月日_元号],
FORMAT([生年月日],'0000-00-00') AS 生年月日,
(CASE WHEN [生年月日] IS NULL THEN '1' ELSE '0' END) AS [生年月日_不詳フラグ], --TODO 月日あたりに '99'が入るも不詳にする？
'' AS [生年月日_不詳表記],
COALESCE([行政区情報].[行政区コード], TblCity.市区町村コード) AS [住所_市区町村コード], --TODO 仮に決め打ち
COALESCE(LEFT( [住所コード],7), '0000000') AS [住所_町字コード],
TblCity.都道府県名 AS [住所_都道府県], --TODO 仮に決め打ち
COALESCE([行政区情報].[行政区名], TblCity.市区町村名) AS [住所_市区群町村名], --TODO 仮に決め打ち
[町丁目マスタ].c_data AS [住所_町字], 
REPLACE(REPLACE(REPLACE([現住所１], TblCity.都道府県名, ''), TblCity.市区町村名, ''),[町丁目マスタ].c_data, '') AS [住所_番地号],
[予備２] AS [住所_方書コード], --[自治会コード]だけれど意味合いが違うかも
[自治会マスタ].c_data AS [住所_方書],
[郵便番号] AS [住所_郵便番号],
FORMAT(
(CASE WHEN [異動情報].[異動事由種別] = 0 AND [転入日] <= [異動情報].[異動日] THEN [異動情報].[異動日]
  ELSE [転入日] END) --TODO 消除の異動日・4ヶ月抹消の予定日と比べて最後になるもの
, '0000-00-00') AS [異動年月日],
'0' AS [異動年月日_不詳フラグ], 
'' AS [異動年月日_不詳表記],
FORMAT(
(CASE WHEN [異動情報].[異動事由種別] = 0 AND [転入日] <= [異動情報].[異動日] THEN [異動情報].[異動届出日]
  ELSE [転入届出日] END) 
, '0000-00-00') AS [異動届出年月日],
((CASE 
WHEN [異動情報].[住基_異動事由] IS NOT NULL THEN [異動情報].[住基_異動事由] 
WHEN [転入日] = [生年月日] THEN '03' --転入日が生年月日と同じなら出生だろうと推測
WHEN [前住所自治体コード] IS NOT NULL THEN '01' --とりあえず国内転入と書いておく
ELSE '07' --改製、が正しいかは不明
END)
) AS [異動事由], --コードは 001(住民基本台帳_基本データリスト)-020~022 に対応させる
(CASE 
WHEN [異動情報].[選挙人名簿異動事由] IS NOT NULL THEN [異動情報].[選挙人名簿異動事由]
--TODO 18歳到達とか転入3ヶ月とかは選挙人台帳の中身でこじつけられるかもしれない
END
) AS [選挙人名簿異動事由], --コードを 007（期日前・不在者投票管理データリスト）-005 に合わせる
NULL AS [選挙人名簿異動年月日],
[異動先自治体マスタ].[h2] AS [転出先住所_市区郡町村名], 
[異動先自治体マスタ].[h7] AS [転出先住所_都道府県名],
--TODO 転入の二重登録通知がらみはどこかにあったか
(CASE WHEN [生年月日] <> [転入日] THEN FORMAT( [転入届出日], '0000-00-00') END) AS [二重登録通知_転入届出年月日],
(CASE WHEN [生年月日] <> [転入日] THEN FORMAT( [転入日], '0000-00-00') END) AS [二重登録通知_登録日（予定日）],
'' AS [二重登録通知_通知受領フラグ],
(CASE WHEN [異動情報].[異動事由コード] = '32' THEN '1' ELSE '0' END)  AS [二重登録照会_移転先の名簿登録有無],
'' AS [二重登録照会_転入届出年月日],
'' AS [二重登録照会_登録日],
'' AS [二重登録照会_備考],
NULL AS [支援措置期間中コード],
(CASE WHEN [異動情報].[異動事由コード] = 21 THEN '1' ELSE '0' END) AS [失権者フラグ],
'' AS [復権予定日], 
(CASE [選挙人属性]
WHEN '2' THEN '1'
WHEN '3' THEN '3'
WHEN '4' THEN '3'
WHEN '5' THEN '2'
END) AS [特定資格登録者],
[選挙人属性詳細].[南極_交付年月日] AS [南極_交付年月日], 
[選挙人属性詳細].[船員_交付年月日] AS [船員_交付年月日], 
[選挙人属性詳細].[郵便等_交付年月日] AS [郵便等_交付年月日],
0 AS [使用媒体コード],
[選挙人属性詳細].[電話番号] AS [電話番号],
(CASE WHEN [投票情報].[滞在地転送先住所１] IS NULL THEN [異動先郵便番号] --滞在地転送先住所がない場合郵便番号が記入されていても異動先を引く
      ELSE [投票情報].[滞在地転送先郵便番号] END) AS [投票用紙の送付先_郵便番号], 
(CASE WHEN [投票情報].[滞在地転送先住所１] IS NULL THEN [異動先住所１] + COALESCE( [異動先住所２],'')  
      ELSE [投票情報].[滞在地転送先住所１] + COALESCE( [投票情報].[滞在地転送先住所２],'') END) AS [投票用紙の送付先_住所],
'' AS [オンライン申請請求フラグ],
'' AS [オンライン申請処理状況],
'' AS [カード用署名用電子証明書_シリアル番号],
COALESCE([投票情報].[UpdateUser], [異動情報].[UpdateUser], [選挙人台帳].[UpdateUser]) AS [操作者ID], 
FORMAT(COALESCE([投票情報].[UpdateTime], [異動情報].[UpdateTime], [選挙人台帳].[UpdateTime]), 'yyyy-MM-dd') AS [操作年月日],
FORMAT(COALESCE([投票情報].[UpdateTime], [異動情報].[UpdateTime], [選挙人台帳].[UpdateTime]), 'HH\:mm\:ss') AS [操作時刻]
FROM (
((((((([選挙人台帳] 
LEFT JOIN (SELECT [個人番号],[名簿区分], [執行日], MAX(c_key) AS [生年元号] 
FROM [選挙人台帳]
LEFT JOIN [和暦マスタ]
ON  [和暦マスタ].c_skey  <= FORMAT( [選挙人台帳].[生年月日], '00000000')
GROUP BY [個人番号], [名簿区分], [執行日])
AS [生年元号リスト]
ON [選挙人台帳].[個人番号] = [生年元号リスト].[個人番号] 
AND [選挙人台帳].[名簿区分] = [生年元号リスト].[名簿区分]
AND [選挙人台帳].[執行日] = [生年元号リスト].[執行日])
LEFT JOIN [町丁目マスタ]
ON LEFT( [選挙人台帳].[住所コード], 3) = [町丁目マスタ].c_key)
LEFT JOIN [自治会マスタ] 
ON [選挙人台帳].[予備２] LIKE [自治会マスタ].c_key + N'%')
LEFT JOIN [KEN_add2] AS [異動先自治体マスタ]
ON FORMAT( [選挙人台帳].[異動先自治体コード], '00000') = [異動先自治体マスタ].[h1])
LEFT JOIN 
(([投票区情報]
LEFT JOIN ([行政区内開票区情報]
LEFT JOIN [行政区情報] ON [行政区内開票区情報].行政区コード=[行政区情報].[行政区コード])
ON [投票区情報].開票区コード=[行政区内開票区情報].[開票区コード])
LEFT JOIN ([選挙区内開票区情報] 
LEFT JOIN [選挙区情報] ON [選挙区内開票区情報].選挙区コード = [選挙区情報].選挙区コード)
ON [投票区情報].開票区コード=[選挙区内開票区情報].開票区コード)
 ON [選挙人台帳].[投票区コード] = [投票区情報].[投票区コード])
LEFT JOIN (SELECT [個人番号]
	  ,MAX(CASE WHEN [選挙人属性] = '5' THEN [交付年月日] END) AS [南極_交付年月日]
	  ,MAX(CASE WHEN [選挙人属性] = '2' THEN [交付年月日] END) AS [船員_交付年月日]
	  ,MAX(CASE WHEN [選挙人属性] IN ('3','4') THEN [交付年月日] END) AS [郵便等_交付年月日]
	  ,MAX([電話番号]) AS [電話番号] 
  FROM [選挙人属性管理情報]
  WHERE [ヘッダー]='00'
  GROUP BY [個人番号]) AS [選挙人属性詳細]
  ON [選挙人台帳].[個人番号] = [選挙人属性詳細].[個人番号]
 )
 LEFT JOIN (SELECT [datamoves].*,
(CASE  
WHEN [DataMoves].[異動事由コード] = 11 THEN 
    '05' --国外転出→表示有権者（都道府県外転出４ヶ月以内）
WHEN [DataMoves].[異動事由コード] = 12 THEN 
	'05' --県外転出→表示有権者（都道府県外転出４ヶ月以内）
--TODO 「４ヶ月以内」でなかった場合
WHEN [DataMoves].[異動事由コード] = 13 THEN 
	'04' --県内転出→表示有権者（都道府県内転出４ヶ月以内）
WHEN [DataMoves].[異動事由コード] = 14 THEN '02' --転出取消→有権者
WHEN [DataMoves].[異動事由コード] = 21 THEN '01' --失権→抹消
WHEN [DataMoves].[異動事由コード] = 31 THEN '01' --誤載→抹消
WHEN [DataMoves].[異動事由コード] = 32 THEN '07' --新住所地→転居者
WHEN [DataMoves].[異動事由コード] = 33 THEN '01' --４ヶ月抹消→抹消
WHEN [DataMoves].[異動事由コード] = 34 THEN '08' --補正登録→新登録者
WHEN [DataMoves].[異動事由コード] = 41 THEN '01' --死亡→抹消
WHEN [DataMoves].[異動事由コード] = 42 THEN '01' --国籍喪失→抹消
WHEN [DataMoves].[異動事由コード] = 43 THEN '01' --失踪宣告→抹消
WHEN [DataMoves].[異動事由コード] = 51 THEN 
	'06' --職権消除→表示有権者（職権消除４ヵ月以内）
WHEN [datamoves].[異動事由コード] = 52 THEN '02' --職権回復→有権者
WHEN [datamoves].[異動事由コード] = 61 THEN '07' --在外移転→転居者
ELSE NULL
END) AS [状態区分],
(CASE 
WHEN [DataMoves].[異動事由コード] = 11 THEN '22' --消除_国外転出
WHEN [DataMoves].[異動事由コード] = 12 THEN '21' --消除_国内転出
WHEN [DataMoves].[異動事由コード] = 13 THEN '21'
WHEN [DataMoves].[異動事由コード] = 14 THEN '09' --記載_異動の取消し（増）
WHEN [DataMoves].[異動事由コード] = 41 THEN '10' --消除_死亡
WHEN [DataMoves].[異動事由コード] = 42 THEN '25' --消除_職権消除（国籍喪失）	日本人住民のみ
WHEN [DataMoves].[異動事由コード] = 43 THEN '10' --失踪宣告→消除_死亡
WHEN [DataMoves].[異動事由コード] = 51 THEN '26' --消除_職権消除
WHEN [DataMoves].[異動事由コード] = 52 THEN '09' --記載_異動の取消し（増）
ELSE NULL --住基由来でないならNULLにしておく
END) AS [住基_異動事由],
(CASE 
WHEN [DataMoves].[異動事由コード] = 11 THEN '18' --変更_転出表示
WHEN [DataMoves].[異動事由コード] = 12 THEN '18' --変更_転出表示
WHEN [DataMoves].[異動事由コード] = 13 THEN '18' --変更_転出表示
WHEN [DataMoves].[異動事由コード] = 14 THEN '19' --変更_再転入（表示消除）
WHEN [DataMoves].[異動事由コード] = 31 THEN '17' --減_誤載
WHEN [DataMoves].[異動事由コード] = 32 THEN '18' --TODO 「新住所地」は「変更_転出表示」？「減_国内転出後4ヶ月経過」？
WHEN [DataMoves].[異動事由コード] = 33 THEN 
      '12' --TODO ４ヶ月抹消されている異動事由を探さないといけない
WHEN [DataMoves].[異動事由コード] = 34 THEN '09' --増_補正登録
WHEN [DataMoves].[異動事由コード] = 61 THEN '16' --減_在外選挙人名簿の登録移転
ELSE NULL
END) AS [選挙人名簿異動事由]
FROM
(SELECT [選挙人異動情報].[個人番号], [選挙人異動情報].[名簿区分], [選挙人異動情報].[執行日],
MIN(優先順位) AS [最優先順位]
FROM [選挙人異動情報]
LEFT JOIN [異動事由コードマスタ]
ON [選挙人異動情報].[異動事由種別] = [異動事由コードマスタ].[異動事由種別]
AND [選挙人異動情報].[異動事由コード] = [異動事由コードマスタ].[異動事由コード]
AND [選挙人異動情報].[名簿区分] = [異動事由コードマスタ].[名簿区分]
GROUP BY [選挙人異動情報].[個人番号], [選挙人異動情報].[名簿区分], [選挙人異動情報].[執行日]) AS [PersonHeader]
LEFT JOIN (
SELECT [選挙人異動情報].[個人番号], [選挙人異動情報].[名簿区分], [選挙人異動情報].[執行日],
[選挙人異動情報].[異動事由種別], [選挙人異動情報].[異動事由コード],
[選挙人異動情報].[異動届出日],　[選挙人異動情報].異動日, [選挙人異動情報].[異動処理日],
[選挙人異動情報].[異動修正区分], [選挙人異動情報].[UpdateUser], [選挙人異動情報].[UpdateTime],
[異動事由コードマスタ].[ホストコード],[異動事由コードマスタ].[優先順位],
[異動事由コードマスタ].[名称],[異動事由コードマスタ].[短縮名], [異動事由コードマスタ].[抄本表記]
FROM
[選挙人異動情報] 
LEFT JOIN [異動事由コードマスタ]
ON [選挙人異動情報].[異動事由種別] = [異動事由コードマスタ].[異動事由種別]
AND [選挙人異動情報].[異動事由コード] = [異動事由コードマスタ].[異動事由コード]
AND [選挙人異動情報].[名簿区分] = [異動事由コードマスタ].[名簿区分]
)
AS [DataMoves]
ON [PersonHeader].[個人番号] = [DataMoves].[個人番号] 
 AND [PersonHeader].[名簿区分] = [DataMoves].[名簿区分]
 AND [PersonHeader].[執行日] = [DataMoves].[執行日]
 AND [PersonHeader].[最優先順位] = [DataMoves].[優先順位]) AS [異動情報]
 ON [異動情報].[個人番号] = [選挙人台帳].個人番号
 AND [異動情報].[名簿区分] = [選挙人台帳].[名簿区分]
 AND [異動情報].[執行日] = [選挙人台帳].[執行日])
 LEFT JOIN [投票情報]                                 --TODO 選挙区コードが参照している投票情報に合うようにしたいがそうすると投票区情報-選挙区情報はこれより後？
 ON [選挙人台帳].[個人番号] = [投票情報].[個人番号]
 AND [選挙人台帳].[名簿区分] = [投票情報].[名簿区分]
 AND [選挙人台帳].[執行日] = [投票情報].[執行日]
 AND [投票情報].[取消日時] IS NULL) 
CROSS JOIN (SELECT MAX(CASE WHEN [c_id] = '17' THEN c_wk END) AS [都道府県コード],
                   MAX(CASE WHEN [c_id] = '17' THEN  c_data  END) AS [都道府県名],
				   MAX(CASE WHEN [c_id] = '18' THEN  c_wk END) AS [市区町村コード],
				   MAX(CASE WHEN [c_id] = '18' THEN c_data END) AS [市区町村名]
		    FROM [SENCOM] 
			WHERE [c_id] IN ('17', '18')) AS TblCity
-- ORDER BY [選挙区コード], [投票区コード], [簿冊番号], [名簿頁番号], [名簿行番号]