日付SQL

WITH 
    [set_param] AS     -- パラメータ「:prmFrom」を基準日、「:prmTo」を末日に変換
    (
        SELECT
            CASE
              WHEN :prmStW <= DATEPART(weekday, :prmFrom) THEN DATEADD(day, (:prmStW - DATEPART(weekday, :prmFrom)), :prmFrom)     -- 当週の基準日(週初日)
              ELSE DATEADD(day, (:prmStW - DATEPART(weekday, :prmFrom)) - 7, :prmFrom)                                             -- 前週の基準日(週初日)
            END AS [from_base_date]
          , CASE
              WHEN :prmStW <= DATEPART(weekday, :prmTo) THEN DATEADD(day, (:prmStW - DATEPART(weekday, :prmTo)) + 6, :prmTo)       -- 当週の末日(週末日)
              ELSE DATEADD(day, (:prmStW - DATEPART(weekday, :prmTo)) - 1, :prmTo)                                                 -- 前週の末日(週末日)
            END AS [to_end_date]
    ),
    [pv_data_ex] AS     -- 日付を基準日に変換
    (
        SELECT
            [pv_data].*
          , CASE
              WHEN :prmStW <= [weekday] THEN DATEADD(day, (:prmStW - [weekday]), [target_date])     -- 当週の基準日(週初日)
              ELSE DATEADD(day, (:prmStW - [weekday]) - 7, [target_date])                           -- 前週の基準日(週初日)
            END AS [base_date]
        FROM
            [XXX_SCHEMA].[pv_data]
          , [set_param]
        WHERE
            [target_date] BETWEEN [set_param].[from_base_date] AND [set_param].[to_end_date]  -- 「:prmFrom」を週初日、「:prmTo」を週末日に要変換
    )
SELECT
    [pv_data_ex].[item_id]
  , [pv_data_ex].[company_id]
  , [pv_data_ex].[base_date]
  , DATEADD(day, 6, [pv_data_ex].[base_date]) AS [end_date]
  , :prmStW AS [start_week]
  , SUM([pv_data_ex].[pv_count]) AS [WEEKLY_PV_PER_ITEM]
  , SUM(SUM([pv_data_ex].[pv_count])) OVER (PARTITION BY [pv_data_ex].[base_date]) AS [WEEKLY_PV]  -- [WEEKLY_PV_PER_ITEM]を基準日毎に集計
FROM
    [pv_data_ex]
GROUP BY
    [pv_data_ex].[base_date]
  , [pv_data_ex].[item_id]
  , [pv_data_ex].[company_id]
ORDER BY
    [pv_data_ex].[base_date]
  , [pv_data_ex].[item_id]
  , [pv_data_ex].[company_id];

WITH 
    [set_param] AS     -- パラメータ「:prmFrom」を基準日、「:prmTo」を末日に変換
    (
        SELECT
            CASE
              WHEN :prmStD <= DATEPART(day, :prmFrom) THEN DATEFROMPARTS(DATEPART(year, :prmFrom), DATEPART(month, :prmFrom), :prmStD)   -- 当月の基準日(月初日)
              ELSE DATEFROMPARTS(DATEPART(year, DATEADD(month, -1, :prmFrom)), DATEPART(month, DATEADD(month, -1, :prmFrom)), :prmStD)   -- 前月の基準日(月初日)
            END AS [from_base_date]
          , CASE
              WHEN :prmStD <= DATEPART(day, :prmTo) THEN DATEADD(day, -1, DATEADD(month, 1, DATEFROMPARTS(DATEPART(year, :prmTo), DATEPART(month, :prmTo), :prmStD)))   -- 当月の末日(月末日)
              ELSE DATEADD(day, -1, DATEADD(month, 1, DATEFROMPARTS(DATEPART(year, DATEADD(month, -1, :prmTo)), DATEPART(month, DATEADD(month, -1, :prmTo)), :prmStD))) -- 前月の末日(月末日)
            END AS [to_end_date]
    ),
    [pv_data_ex] AS     -- 日付を基準日に変換
    (
        SELECT
            [pv_data].*
          , CASE
              WHEN :prmStD <= DATEPART(day, [target_date]) THEN DATEFROMPARTS(DATEPART(year, [target_date]), DATEPART(month, [target_date]), :prmStD)   -- 当月の基準日(月初日)
              ELSE DATEFROMPARTS(DATEPART(year, DATEADD(month, -1, [target_date])), DATEPART(month, DATEADD(month, -1, [target_date])), :prmStD)        -- 前月の基準日(月初日)
            END AS [base_date]
        FROM
            [XXX_SCHEMA].[pv_data]
          , [set_param]
        WHERE
            [target_date] BETWEEN [set_param].[from_base_date] AND [set_param].[to_end_date]  -- 「:prmFrom」を月初日、「:prmTo」を月末日に要変換
    )
SELECT
    [pv_data_ex].[item_id]
  , [pv_data_ex].[company_id]
  , [pv_data_ex].[base_date]
  , DATEADD(day, -1, DATEADD(month, 1, [pv_data_ex].[base_date])) AS [end_date]
  , :prmStD AS [start_day]
  , SUM([pv_data_ex].[pv_count]) AS [MONTHLY_PV_PER_ITEM]
  , SUM(SUM([pv_data_ex].[pv_count])) OVER (PARTITION BY [pv_data_ex].[base_date]) AS [MONTHLY_PV]  -- [MONTHLY_PV_PER_ITEM]を基準日毎に集計
FROM
    [pv_data_ex]
GROUP BY
    [pv_data_ex].[base_date]
  , [pv_data_ex].[item_id]
  , [pv_data_ex].[company_id]
ORDER BY
    [pv_data_ex].[base_date]
  , [pv_data_ex].[item_id]
  , [pv_data_ex].[company_id];