SELECT 
[市区町村コード], 
TblCity.市区町村コード + [行政区情報].行政区コード AS [指定都市_行政区コード],
COALESCE([選挙区情報].選挙区コード, '001') AS [選挙区コード],
[選挙人台帳].[投票区コード],
FORMAT([選挙人台帳].[簿冊番号],'000') AS [簿冊番号],
FORMAT([選挙人台帳].[頁番号], '00000') AS [名簿頁番号],
FORMAT([選挙人台帳].[行番号],'00000') AS [名簿行番号],
FORMAT([選挙人台帳].登録日, '0000-00-00') AS [登録基準日コード],
[Votes].[投票所コード] AS [投票所コード],
FORMAT([不在者投票].[請求日], 'yyyy-MM-dd') AS  [不在者投票_請求日],
[不在者投票].[請求者] AS  [不在者投票_請求者],
[不在者投票].[請求方法] AS [不在者投票_請求方法], --Terabbit のデータベースでは [受付区分]
FORMAT([不在者投票].[交付日], 'yyyy-MM-dd')  AS [不在者投票_交付日],
FORMAT([不在者投票].[交付時刻], 'HH\:mm\:ss') AS [不在者投票_交付時刻],
FORMAT([不在者投票].[受理日], 'yyyy-MM-dd') AS [不在者投票_受理日],
[不在者投票].[投票方法] AS [不在者投票_投票方法],
[不在者投票].[記載場所] AS [不在者投票_記載場所],
不在者投票_返還の有無,
不在者投票_代理投票立会人,
不在者投票_代理投票補助者,
FORMAT([期日前投票].[投票日], 'yyyy-MM-dd') AS [期日前投票_投票日],
FORMAT([期日前投票].[投票時刻], 'HH\:mm\:ss') AS  [期日前投票_投票時刻],
期日前投票_投票方法,
期日前投票_仮投票,
期日前投票_代理投票立会人,
期日前投票_代理投票補助者,
期日前投票_返還の有無,
FORMAT([当日投票].[受付時刻], 'HH\:mm\:ss') AS [当日投票_受付時刻],
当日投票_投票方法,
当日投票_仮投票,
当日投票_代理投票立会人,
当日投票_代理投票補助者,
当日投票_返還の有無,
当日投票_共通投票所利用フラグ,
[Votes].[操作者ID] AS [操作者ID],
[Votes].[操作年月日] AS [操作年月日],
[Votes].[操作時刻] AS [操作時刻]
FROM (SELECT c_wk AS [都道府県コード], c_data AS [都道府県名] FROM [SENCOM] WHERE [c_id] = '17') AS TblPref
CROSS JOIN (SELECT c_wk AS [市区町村コード], c_data AS [市区町村名] FROM [SENCOM] WHERE [c_id]='18') AS TblCity
CROSS JOIN (
((((SELECT [個人番号], [名簿区分],[執行日],
MAX(CASE WHEN [投票状態] = '1' THEN [不在交付場所]
      WHEN [投票状態] = '2' THEN [不在受理場所]
	  WHEN [投票状態] = '6' THEN [投票場所]
	  WHEN [投票状態] = '7' THEN [投票場所]
END) AS [投票所コード],
MAX([UpdateUser]) AS [操作者ID], --TODO [取消日時]がNULLの行が1行ならこれで通るはず
FORMAT(MAX([UpdateTime]), 'yyyy-MM-dd') AS [操作年月日],
FORMAT(MAX([UpdateTime]), 'HH\:mm\:ss') AS [操作時刻]
FROM [投票情報]
WHERE [投票情報].[取消日時] IS NULL
GROUP BY [個人番号], [名簿区分],[執行日])AS [Votes]
LEFT JOIN (
SELECT [個人番号], [名簿区分],[執行日],
MAX([不在請求日]) AS [請求日],
       MAX(CASE WHEN [不在請求者区分] IN ('1','99') THEN '2'
	     ELSE '1'
	     END) AS [請求者],
MAX(CASE WHEN [受付区分] = '11' THEN '01'
         WHEN [受付区分] = '12' THEN '09'
		 WHEN [受付区分] = '13' THEN '02'
		 WHEN [受付区分] = '14' AND [投票事由] <> '8' THEN '07'
		 WHEN [受付区分] = '14' AND [投票事由] = '8' THEN '08'
		 WHEN [受付区分] = '15' THEN '10'
		 WHEN [受付区分] = '16' THEN '06'
		 WHEN [受付区分] = '51' THEN '03' --船舶
		 WHEN [受付区分] = '52' THEN '03'
		 WHEN [受付区分] = '53' THEN '04'
		 WHEN [受付区分] = '54' THEN '05'
END) AS [請求方法],
MAX([不在交付日]) AS [交付日],
MAX([不在交付処理日時]) AS [交付時刻],
MAX(CASE WHEN [投票状態] IN ('2', '9') THEN [不在受理日] END) AS [受理日],
MAX(CASE WHEN [投票状態] IN ('1', '2') THEN [投票方法] END) AS [投票方法],
'' AS [記載場所], --TODO 受付場所？施設だったら施設？郵便の住所は？
(CASE WHEN COUNT([返票事由]) > 0 THEN '1' ELSE '0' END) AS  [不在者投票_返還の有無],
MAX([投票立会人１]) [不在者投票_代理投票立会人],
MAX([代理補助者氏名１]) AS [不在者投票_代理投票補助者]
FROM [投票情報]
WHERE [投票状態] IN ('1','2','8','9') 
  AND [取消日時] IS NULL
GROUP BY [個人番号], [名簿区分],[執行日]) AS [不在者投票]
ON [Votes].[個人番号] = [不在者投票].[個人番号]
AND [Votes].[名簿区分] = [不在者投票].[名簿区分]
AND [Votes].[執行日] = [不在者投票].[執行日])
LEFT JOIN (SELECT [個人番号], [名簿区分],[執行日],
MAX([投票日]) AS [投票日],　
MAX([投票処理日時]) AS [投票時刻],
MAX([投票方法]) AS [期日前投票_投票方法],
(CASE WHEN COUNT(
	CASE WHEN [投票状態] = '4' AND [受付区分] = '01' THEN 1 END
	) > 0 
THEN '1' ELSE '0' END) AS [期日前投票_仮投票],
MAX([投票立会人１]) AS [期日前投票_代理投票立会人],
MAX([代理補助者氏名１]) AS [期日前投票_代理投票補助者],
(CASE WHEN COUNT( [返票事由]) > 0 
	 THEN '1' ELSE '0' END) AS [期日前投票_返還の有無]
FROM [投票情報]
WHERE ([投票状態] = '6' OR ([投票状態]='4' AND [受付区分] = '01'))
AND [取消日時] IS NULL
GROUP BY  [個人番号], [名簿区分],[執行日]) AS [期日前投票]
ON [Votes].[個人番号] = [期日前投票].[個人番号]
AND [Votes].[名簿区分] = [期日前投票].[名簿区分]
AND [Votes].[執行日] = [期日前投票].[執行日])
LEFT JOIN (
SELECT [個人番号], [名簿区分],[執行日],
MAX([投票処理日時]) AS [受付時刻],
MAX([投票方法]) AS [当日投票_投票方法],
(CASE WHEN COUNT(
	CASE WHEN [投票状態] = '4' AND [受付区分] = '02' THEN 1 END
	) > 0 
THEN '1' ELSE '0' END) AS [当日投票_仮投票],
MAX([投票立会人１]) AS [当日投票_代理投票立会人],
MAX([代理補助者氏名１]) AS [当日投票_代理投票補助者],
(CASE WHEN COUNT([返票事由]) > 0 
	 THEN '1' ELSE '0' END) AS [当日投票_返還の有無],
MAX(CASE WHEN [投票場所] LIKE 'A%' THEN '1' ELSE '0' END) AS [当日投票_共通投票所利用フラグ]
 FROM [投票情報]
WHERE ([投票状態] = '7' OR ([投票状態] = '4' AND [受付区分] = '02'))
AND [投票情報].[取消日時] IS NULL
GROUP BY [個人番号], [名簿区分],[執行日]) AS [当日投票]
ON [Votes].[個人番号] = [当日投票].[個人番号]
AND [Votes].[名簿区分] = [当日投票].[名簿区分]
AND [Votes].[執行日] = [当日投票].[執行日])
) 
LEFT JOIN ([選挙人台帳]
LEFT JOIN 
(([投票区情報]
LEFT JOIN ([行政区内開票区情報]
LEFT JOIN [行政区情報] ON [行政区内開票区情報].行政区コード=[行政区情報].[行政区コード])
ON [投票区情報].開票区コード=[行政区内開票区情報].[開票区コード])
LEFT JOIN ([選挙区内開票区情報] 
LEFT JOIN [選挙区情報] ON [選挙区内開票区情報].選挙区コード = [選挙区情報].選挙区コード)
ON [投票区情報].開票区コード=[選挙区内開票区情報].開票区コード)
 ON [選挙人台帳].[投票区コード] = [投票区情報].[投票区コード])
ON [Votes].[個人番号] = [選挙人台帳].[個人番号]
AND [Votes].[名簿区分] = [選挙人台帳].[名簿区分]
AND [Votes].[執行日] = [選挙人台帳].執行日
