https://keijiban.umayadia.com/ThreadDetail.aspx?ThreadId=30227#CommentId82738
投稿者 魔界の仮面弁士  (社会人) 投稿日時 2017/2/17 18:57:13
訂正：

今回は日付の降順なので、ROW_NUMBER() のパーティションを
ORDER BY [実施日] から ORDER BY [実施日] DESC に
変更しておいてください。


また、先の SQL では、同日に 2 回の訪問があった場合を考慮していません。

たとえば '27-001' の ID に対して
　2016/04/07
　2016/04/05
　2016/04/07
といった多重訪問の記録があった場合に
27-001　　2016/04/07　　2016/04/07
としたいのならば、先の例（を DESC に修正したもの）で良いのですが、
27-001　　2016/04/07　　2016/04/05
としたいのであれば、『ROW_NUMBER() 関数』の代わりに
『DENSE_RANK() 関数』を使ってみてください。

―――ということで、DENSE_RANK を使ったパターンも組んでみました。


案５が CREATE TABLE 、案６が INSERT だったので、
ついでに今回は INSERT & UPDATE にしてみます。


【案７】
WITH
  [ID_LIST] AS (SELECT DISTINCT [ID] FROM [Aテーブル]),
  [DT_LIST] AS (
    SELECT
      [ID]
    , [実施日]
    , [行番号] = DENSE_RANK() OVER (PARTITION BY [ID] ORDER BY [実施日] DESC)
    FROM  [Aテーブル]
    WHERE [業務内容] = N'訪問'
  ),
  [RESULT] AS (
    SELECT
      [ID_LIST].[ID]
    , MAX(CASE [行番号] WHEN 1 THEN [実施日] END) AS [実施日１]
    , MAX(CASE [行番号] WHEN 2 THEN [実施日] END) AS [実施日２]
    FROM
      [ID_LIST] LEFT JOIN [DT_LIST]
      ON [ID_LIST].[ID] = [DT_LIST].[ID]
      AND [DT_LIST].[行番号] <= 2
    GROUP BY
      [ID_LIST].[ID]
  )
MERGE INTO [Bテーブル]
  USING [RESULT]
     ON ([Bテーブル].[ID] = [RESULT].[ID])
  WHEN MATCHED THEN
    UPDATE SET
      [実施日１] = [RESULT].[実施日１]
    , [実施日２] = [RESULT].[実施日２]
  WHEN NOT MATCHED THEN
    INSERT ([ID], [実施日１], [実施日２])
    VALUES (
      [RESULT].[ID]
    , [RESULT].[実施日１]
    , [RESULT].[実施日２]
    )
;
0