SELECT 
(SELECT [c_wk] AS [市区町村コード] FROM [SENCOM] WHERE [c_id]='18') AS [市区町村コード], 
(SELECT [c_wk] AS [市区町村コード] FROM [SENCOM] WHERE [c_id]='18') & [行政区情報].[行政区コード] AS [指定都市_行政区コード],
IIf([選挙区情報].[選挙区コード] IS NULL, '001', [選挙区情報].[選挙区コード]) AS [選挙区コード],
[選挙人台帳].[投票区コード],
FORMAT([選挙人台帳].[簿冊番号],'000') AS [簿冊番号],
FORMAT([選挙人台帳].[頁番号], '00000') AS [名簿頁番号],
FORMAT([選挙人台帳].[行番号],'00000') AS [名簿行番号],
FORMAT([選挙人台帳].[登録日], '0000-00-00') AS [登録基準日コード],
[不在施設番号] AS [施設コード],
([選挙人台帳].[投票区コード] &
FORMAT([選挙人台帳].[簿冊番号],'000') &
FORMAT([選挙人台帳].[頁番号], '00000') &
FORMAT([選挙人台帳].[行番号],'00000')) AS [名簿番号],
IIF([異動情報].[状態区分] IS NULL, '02', [異動情報].[状態区分]) AS [状態区分], 
[漢字氏名] AS [氏名],
IIf(InStr(1, [漢字氏名], '　', 1) > 0, LEFT([漢字氏名], InStr(1, [漢字氏名], '　', 1) - 1), '') AS [氏_日本人],
IIf(InStr(1, [漢字氏名], '　', 1) > 0, Mid([漢字氏名], InStr(1, [漢字氏名], '　', 1) + 1), '') AS [名_日本人],
[カナ氏名] AS [氏名_フリガナ],
IIF(InStr(1, [カナ氏名], '　', 1) > 0, LEFT([カナ氏名], InStr(1, [カナ氏名], '　', 1) - 1), '') AS [氏_日本人_フリガナ],
IIF(InStr(1, [カナ氏名], '　', 1) > 0, Mid([カナ氏名],  InStr(1, [カナ氏名], '　', 1) + 1), '') AS [名_日本人_フリガナ],
[性別], 
Switch( [性別] = 1, '男', [性別] = 2, '女') AS [性別表記],
(SELECT MAX([c_key])  
FROM [和暦マスタ] 
WHERE  CLng([c_skey]) <= [選挙人台帳].[生年月日]) AS [生年月日_元号], 
FORMAT([選挙人台帳].[生年月日],'0000-00-00') AS 生年月日,
IIF([選挙人台帳].[生年月日] IS NULL,'1', '0') AS [生年月日_不詳フラグ],
'' AS [生年月日_不詳表記],
IIf([行政区情報].[行政区コード] IS NULL, (SELECT [c_wk] AS [市区町村コード] FROM [SENCOM] WHERE [c_id]='18'), [行政区情報].[行政区コード]) AS [住所_市区町村コード],
IIF([住所コード] IS NULL OR [住所コード] = '', '0000000', LEFT([住所コード],7)) AS [住所_町字コード],
 (SELECT  [c_data] AS [都道府県名] FROM [SENCOM] WHERE [c_id] = '17') AS [住所_都道府県],
IIF([行政区情報].[行政区名] IS NULL, (SELECT [c_data] AS [市区町村名] FROM [SENCOM] WHERE [c_id]='18'), [行政区情報].[行政区名]) AS [住所_市区群町村名],
[町丁目マスタ].[c_data] AS [住所_町字], 
Switch(
 [現住所１] 
    LIKE (SELECT MAX(IIF([c_id]='17', [c_data], NULL)) 
               & MAX(IIF([c_id]='18', [c_data], NULL))
               FROM [SENCOM] WHERE [c_id] IN ('17', '18')) & '%',
  MID([現住所１], (SELECT SUM(LEN([c_data])) FROM [SENCOM] WHERE [c_id] IN ('17','18'))),
  [現住所１] LIKE (SELECT [c_data] FROM [SENCOM] WHERE [c_id] = '18') & '%',
  MID([現住所１], (SELECT LEN([c_data]) FROM [SENCOM] WHERE [c_id] = '18')),
  (1=1), [現住所１]
)
AS [住所_番地号],
[予備２] AS [住所_方書コード], 
[自治会マスタ].[c_data] AS [住所_方書],
[郵便番号] AS [住所_郵便番号],
FORMAT(
IIF([異動情報].[異動事由種別] = 0 AND [転入日] <= [異動情報].[異動日],[異動情報].[異動日], [転入日]) 
, '0000-00-00') AS [異動年月日],
'0' AS [異動年月日_不詳フラグ], 
'' AS [異動年月日_不詳表記],
FORMAT(
IIF([異動情報].[異動事由種別] = 0 AND [転入日] <= [異動情報].[異動日], [異動情報].[異動届出日], [転入届出日]) 
, '0000-00-00') AS [異動届出年月日],
(Switch( 
 [異動情報].[住基_異動事由] IS NOT NULL, [異動情報].[住基_異動事由] ,
 [転入日] = [選挙人台帳].[生年月日], '03' ,
 [前住所自治体コード] IS NOT NULL,  '01', 
(1=1), '07')
) AS [異動事由], 
IIF([異動情報].[選挙人名簿異動事由] IS NOT NULL, [異動情報].[選挙人名簿異動事由], NULL) AS [選挙人名簿異動事由], 
IIf([異動情報].[異動日] IS NOT NULL, FORMAT( [異動情報].[異動日], '0000-00-00')) AS [選挙人名簿異動年月日],
[異動先自治体マスタ].[h2] AS [転出先住所_市区郡町村名], 
[異動先自治体マスタ].[h7] AS [転出先住所_都道府県名],
'' AS [二重登録通知_転入届出年月日],
'' AS [二重登録通知_登録日（予定日）],
'' AS [二重登録通知_通知受領フラグ],
'' AS [二重登録照会_移転先の名簿登録有無],
'' AS [二重登録照会_転入届出年月日],
'' AS [二重登録照会_登録日],
'' AS [二重登録照会_備考],
NULL AS [支援措置期間中コード],
IIF([異動情報].[異動事由コード] = 21, '1', '0') AS [失権者フラグ],
IIF([異動情報].[異動事由コード] = 21, NULL, NULL) AS [復権予定日], 
Switch([選挙人台帳].[選挙人属性] = '2', '1',
       [選挙人台帳].[選挙人属性] = '3', '3',
       [選挙人台帳].[選挙人属性] = '4', '3',
       [選挙人台帳].[選挙人属性] = '5', '2'
) AS [特定資格登録者],
[選挙人属性詳細].[南極_交付年月日] AS [南極_交付年月日], 
[選挙人属性詳細].[船員_交付年月日] AS [船員_交付年月日], 
[選挙人属性詳細].[郵便等_交付年月日] AS [郵便等_交付年月日],
0 AS [使用媒体コード],
[選挙人属性詳細].[電話番号] AS [電話番号],
IIf([滞在地転送先住所１] IS NULL, [異動先郵便番号], [滞在地転送先郵便番号]) AS [投票用紙の送付先_郵便番号], 
IIF([滞在地転送先住所１] IS NULL, [異動先住所１] + IIf([異動先住所２] IS NULL, '',[異動先住所２]),  
      [滞在地転送先住所１] + IIf([滞在地転送先住所２] IS NULL, '', [滞在地転送先住所２])) AS [投票用紙の送付先_住所],
'' AS [オンライン申請請求フラグ],
'' AS [オンライン申請処理状況],
'' AS [カード用署名用電子証明書_シリアル番号],
Switch([投票情報].[UpdateUser] IS NOT NULL, [投票情報].[UpdateUser],
       [異動情報].[UpdateUser] IS NOT NULL, [異動情報].[UpdateUser], 
       (1=1), [選挙人台帳].[UpdateUser]) AS [操作者ID], 
FORMAT(Switch([投票情報].[UpdateTime] IS NOT NULL, [投票情報].[UpdateTime],  
              [異動情報].[UpdateTime] IS NOT NULL, [異動情報].[UpdateTime],
              (1=1), [選挙人台帳].[UpdateTime]), 'yyyy-MM-dd') AS [操作年月日],
FORMAT(Switch([投票情報].[UpdateTime] IS NOT NULL, [投票情報].[UpdateTime], 
              [異動情報].[UpdateTime] IS NOT NULL, [異動情報].[UpdateTime],
              (1=1), [選挙人台帳].[UpdateTime]), 'HH\:mm\:ss') AS [操作時刻]
FROM  (
((((([選挙人台帳] 
LEFT JOIN [町丁目マスタ]
ON [選挙人台帳].[住所コード] LIKE [町丁目マスタ].[c_key] + '%')
LEFT JOIN [自治会マスタ] 
ON [選挙人台帳].[予備２] LIKE [自治会マスタ].[c_key] + '%')
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(IIF( [選挙人属性] = '5', [交付年月日],Null)) AS [南極_交付年月日]
	  ,MAX(IIF( [選挙人属性] = '2', [交付年月日], NUll)) AS [船員_交付年月日]
	  ,MAX(IIf([選挙人属性] IN ('3','4'), [交付年月日],Null)) AS [郵便等_交付年月日]
	  ,MAX([選挙人属性管理情報].[電話番号]) AS [電話番号] 
  FROM [選挙人属性管理情報]
  WHERE [ヘッダー]='00'
  GROUP BY [個人番号]) AS [選挙人属性詳細]
  ON [選挙人台帳].[個人番号] = [選挙人属性詳細].[個人番号]
 )
 LEFT JOIN (SELECT [DataMoves].*,
Switch([DataMoves].[異動事由種別] = 0,
Switch(  
 [DataMoves].[異動事由コード] = 11,  '05', 
 [DataMoves].[異動事由コード] = 12,  	'05', 
 [DataMoves].[異動事由コード] = 13,  	'04',
 [DataMoves].[異動事由コード] = 14,  '02', 
 [DataMoves].[異動事由コード] = 41,  '01', 
 [DataMoves].[異動事由コード] = 42,  '01', 
 [DataMoves].[異動事由コード] = 43,  '01', 
 [DataMoves].[異動事由コード] = 51,  	'06', 
 [Datamoves].[異動事由コード] = 52,  '02')
,
[DataMoves].[異動事由種別] = 1,
Switch(  
 [DataMoves].[異動事由コード] = 21,  '01', 
 [DataMoves].[異動事由コード] = 31,  '01',
 [DataMoves].[異動事由コード] = 32,  '07', 
 [DataMoves].[異動事由コード] = 33,  '01', 
 [DataMoves].[異動事由コード] = 34,  '08', 
 [Datamoves].[異動事由コード] = 61,  '07')
) AS [状態区分],
Switch(
 [DataMoves].[異動事由コード] = 11,  '22',
 [DataMoves].[異動事由コード] = 12,  '21', 
 [DataMoves].[異動事由コード] = 13,  '21',
 [DataMoves].[異動事由コード] = 14,  '09', 
 [DataMoves].[異動事由コード] = 41,  '10', 
 [DataMoves].[異動事由コード] = 42,  '25', 
 [DataMoves].[異動事由コード] = 43,  '10', 
 [DataMoves].[異動事由コード] = 51,  '26', 
 [DataMoves].[異動事由コード] = 52,  '09' 
) AS [住基_異動事由],
Switch( 
 [DataMoves].[異動事由コード] = 11,  '18', 
 [DataMoves].[異動事由コード] = 12,  '18', 
 [DataMoves].[異動事由コード] = 13,  '18', 
 [DataMoves].[異動事由コード] = 14,  '19', 
 [DataMoves].[異動事由コード] = 31,  '17',
 [DataMoves].[異動事由コード] = 32,  '18', 
 [DataMoves].[異動事由コード] = 33,  '12', 
 [DataMoves].[異動事由コード] = 34,  '09', 
 [DataMoves].[異動事由コード] = 61,  '16' 
) AS [選挙人名簿異動事由]
FROM
(SELECT [PHMV].[個人番号], [PHMV].[名簿区分], [PHMV].[執行日],
MIN(優先順位) AS [最優先順位]
FROM [選挙人異動情報] AS [PHMV]
LEFT JOIN [異動事由コードマスタ] AS [PHMM]
ON [PHMV].[異動事由種別] = [PHMM].[異動事由種別]
AND [PHMV].[異動事由コード] = [PHMM].[異動事由コード]
AND [PHMV].[名簿区分] = [PHMM].[名簿区分]
GROUP BY [PHMV].[個人番号], [PHMV].[名簿区分], [PHMV].[執行日]) 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  [投票情報] 
 ON ([選挙人台帳].[個人番号] = [投票情報].[個人番号]
 AND [選挙人台帳].[名簿区分] = [投票情報].[名簿区分]
 AND [選挙人台帳].[執行日] = [投票情報].[執行日]
 AND [投票情報].[取消日時] IS NULL)
  