时间戳

首页 > 解决方案 > 如何为每个员工总结 6 个月范围内 5 年的数据？

sql - 如何为每个员工总结 6 个月范围内 5 年的数据？

问题描述

我是 SQL 编程新手，需要您的帮助。我需要编写一个 sql 查询，从他加入公司的那一天开始，为每个员工每 6 个月和 5 年之前的累计金额求和。

我的表如下所示

Employee    StartDate   DealDate    Amount
1           10/01/2017  11/01/2017  10000    --Starting month
1           10/01/2017  11/02/2017  15000    --within first 6 month of joining
1           10/01/2017  11/07/2017  20000    --6-12 months
1           10/01/2017  11/01/2018  30000    --12-18 months
1           10/01/2017  11/02/2018  40000    --12-18 months
1           10/01/2017  11/07/2018  50000    --18-24 months
1           10/01/2017  11/01/2019  60000    --24-30 months
1           10/01/2017  11/08/2019  70000    --30-36 months
1           10/01/2017  11/01/2020  80000    --36-42 months
1           10/01/2017  11/07/2020  90000    --42-48 months
2           20/01/2017  11/01/2017  10000    --so on for employee 2
2           20/01/2017  11/02/2017  25000
2           20/01/2017  11/07/2017  40000
2           20/01/2017  11/01/2018  30000
2           20/01/2017  11/02/2018  40000
2           20/01/2017  11/07/2018  50000
2           20/01/2017  11/01/2019  60000
2           20/01/2017  11/08/2019  70000
2           20/01/2017  11/01/2020  80000

and my expected outcome is

Employee    StartDate   [0-6month]  [6-12month] [1 year]    [12-18 month]   [18-24 month]   [2 year]
1           10/01/2017  25000       20000       45000       70000           50000           165000
2           20/01/2017  35000       40000       75000       70000           50000           195000

我尝试了以下


DECLARE @range1start DATE = @date
DECLARE @range1end DATE = (SELECT DATEADD(m,6,@range1start))
DECLARE @range2start DATE = @range1end
DECLARE @range2end DATE = (SELECT DATEADD(m,6,@range2start))
DECLARE @range3start DATE = @range2end
DECLARE @range3end DATE = (SELECT DATEADD(m,12,@range3start)) '

然后对每个范围使用 case 条件。我想知道是否有更好的方法来实现这个而不使用这么多变量和循环。任何帮助表示赞赏。

提前致谢！

标签： sqlsql-serversql-server-2008sumpivot

解决方案

您可以使用条件聚合 - 尽管键入有点冗长：

select
    employee,
    min(startdate) startdate,
    sum(case when dealdate < dateadd(month, 6, startdate) then amount end) [0-6 month],
    sum(case when dealdate >= dateadd(month, 6, startdate) and dealdate < dateadd(month, 12, startdate) then amount end) [6-12 month],
    sum(case when dealdate < dateadd(month, 12, startdate) then amount end) [1 year],
    sum(case when dealdate >= dateadd(month, 12, startdate) and dealdate < dateadd(month, 18, startdate) then amount end) [12-18 month],
    sum(case when dealdate >= dateadd(month, 18, startdate) and dealdate < dateadd(month, 24, startdate) then amount end) [18-24 month],
    sum(case when dealdate >= dateadd(month, 12, startdate) and dealdate < dateadd(month, 24, startdate) then amount end) [2 year]
from mytable
group by employee

推荐阅读