MySQLでタスクの開始日と終了日に基づいて四半期ごとの値の合計を計算するにはどうすればよいですか？

debugcn 投稿 Dev

ムラリ

Type、StartDate、EndDate、Amountのアクティビティテーブルがあります。開始日と終了日を使用して、すべての四半期のタイプ別にグループ化された金額の合計を計算したいと思います。

たとえば、StartDateがあり、EndDateが1月から3月の間にある場合、その四半期に関連するすべてのレコードの金額の合計を計算する必要があります。

そして、残りのレコードについても同じことをしたいと思います。

CREATE TABLE activity
(id int(11)
,Type varchar(10)
,StartDate date
,EndDate date
,Amount int(11)
);
                                                                                         
INSERT INTO activity VALUES
(1,'Type1','2021-01-15','2021-02-25',10000),
(2,'Type1','2021-01-25','2021-02-25',10000),
(3,'Type2','2021-08-05','2021-09-25',15000),
(4,'Type3','2021-10-15','2021-12-25',5000);

これは期待される出力です。

期待される出力：

Type    T1      T2      T3      T4
1       2000    0       0       0
2       0       2000    0       0
3       0       0       0       3000


T1,T2,T3,T4 are quarters of Year

T1 -> Jan TO Mar
T2 -> April TO June
T3 -> July TO September
T4 -> October TO December

クエリを試しました。私はそのクエリをオンラインエディタで行いました。

これは、サンプルデータを含むオンラインエディターのリンクです。

正しい応答を取得するためにクエリに欠けているものはありますか？

Tim Biegeleisen

この要件は、各四半期の日付範囲を維持するカレンダーテーブルを使用して処理できます。

SELECT
    a.Type,
    SUM(CASE WHEN quarter = 'T1' THEN a.Amount ELSE 0 END) AS T1,
    SUM(CASE WHEN quarter = 'T2' THEN a.Amount ELSE 0 END) AS T2,
    SUM(CASE WHEN quarter = 'T3' THEN a.Amount ELSE 0 END) AS T3,
    SUM(CASE WHEN quarter = 'T4' THEN a.Amount ELSE 0 END) AS T4
FROM Activity a
INNER JOIN
(
    SELECT 'T1' AS quarter, '2021-01-01' AS QuarterStart, '2021-04-01' AS QuarterEnd UNION ALL
    SELECT 'T2', '2021-04-01', '2021-07-01' UNION ALL
    SELECT 'T3', '2021-07-01', '2021-10-01' UNION ALL
    SELECT 'T4', '2021-10-01', '2022-01-01'
) q
    ON a.StartDate < q.QuarterEnd AND a.EndDate >= q.QuarterStart
GROUP BY
    a.Type;