SELECT FORMAT([投票情報].[執行日], 'yyyy-MM-dd') AS [執行日],
       [選挙情報].[選挙名称] AS [選挙名],
	   (CASE WHEN [投票情報].[利用者証明書シリアルNo] IS NULL THEN 0 ELSE 1 END) AS [マイナンバー受付実施有無],
	   [選挙人台帳].[投票区コード] +
	   (CASE WHEN [選挙人台帳].[簿冊番号] IS NULL OR [選挙人台帳].[簿冊番号] <= 0 THEN '' 
	        ELSE '-' + CAST([選挙人台帳].[簿冊番号] AS varchar) END) +
	   (CASE WHEN [選挙人台帳].[頁番号] IS NULL OR [選挙人台帳].[頁番号] <= 0 THEN '' 
	        ELSE '-' + CAST([選挙人台帳].[頁番号] AS varchar) END) + 
       '-' + CAST([選挙人台帳].[行番号] AS varchar) AS [名簿番号],
	   FORMAT([選挙人台帳].[生年月日], '0000-00-00') AS [生年月日],
	   [選挙人台帳].[漢字氏名] AS [氏名],
	   [選挙人台帳].[カナ氏名] AS [ﾌﾘｶﾞﾅ],
	   (CASE [選挙人台帳].[性別]  WHEN 1 THEN '男' WHEN 2 THEN '女' END) AS [性別] ,
	   [選挙人台帳].[現住所１] + COALESCE([選挙人台帳].[現住所２], '') AS [住所],
	   FORMAT([投票情報].[不在請求日], 'yyyy-MM-dd') AS [請求日],
	   [受付区分マスタ].[c_data] AS [請求方法],
	   FORMAT([投票情報].[不在交付日], 'yyyy-MM-dd') AS [交付日],
	   FORMAT([投票情報].[不在受理日], 'yyyy-MM-dd') AS [受理日],
	   [投票方法マスタ].[c_data] AS [投票方法],
	   FORMAT([投票情報].[投票日], 'yyyy-MM-dd') AS [投票日],
	   FORMAT([投票情報].[投票処理日時], 'HH:mm') AS [投票時刻],
	   [投票状態マスタ].[c_data] AS [投票状態],
	   [受付場所情報].[受付場所名] AS [投票場所]
FROM ((((([投票情報] 
LEFT JOIN [選挙情報]
	   ON [投票情報].[選挙ID] = [選挙情報].[ID])
LEFT JOIN [受付区分マスタ] 
       ON [投票情報].[受付区分] = [受付区分マスタ].[c_key])
LEFT JOIN [投票方法マスタ]
       ON [投票情報].[投票方法] = [投票方法マスタ].[c_key])
LEFT JOIN [投票状態マスタ]
　　　 ON [投票情報].[投票状態] = [投票状態マスタ].[c_key])
LEFT JOIN [受付場所情報]
       ON [受付場所情報].[受付場所コード] = 
	       (CASE [投票情報].[投票状態] 
	             WHEN '6' THEN [投票情報].[投票場所]
		         WHEN '2' THEN [投票情報].[不在受理場所]
		         WHEN '1' THEN [投票情報].[不在交付場所] END))
LEFT JOIN [選挙人台帳] 
      ON [投票情報].[個人番号] = [選挙人台帳].[個人番号]
	 AND [投票情報].[名簿区分] = [選挙人台帳].[名簿区分]
	 AND [投票情報].[執行日] = [選挙人台帳].[執行日]
WHERE [投票情報].[取消日時] IS NULL and [投票情報].[利用者証明書シリアルNo] IS NOT NULL
ORDER BY [選挙人台帳].[投票区コード], [選挙人台帳].[簿冊番号], [選挙人台帳].[頁番号], [選挙人台帳].[行番号]