时间戳

首页 > 解决方案 > 前一个月的动态 12 个月滚动总计，可追溯到 12 个月

sql - 前一个月的动态 12 个月滚动总计，可追溯到 12 个月

问题描述

我有以下 sql 代码（where 子句只是为了限制当前的行）

select 
    month,
    monthname,
    year,
    count(distinct case when a.dim_service_type_id_desc like '%Direct Payment%' then a.DIM_PERSON_ID else null end) as No_dp,
    count(distinct a.DIM_PERSON_ID) as no_ppl
from   
    SERVICE_PROVISIONS a
    inner join date_tbl d on CONVERT(VARCHAR(35),a.start_dttm,112) = d.dim_date_id
where 
    a.dim_person_id >0
    and year = 2018
group by 
    month,
    monthname,
    year

我的输出是这个

month monthname     year    No_dp   no_ppl
1       January     2018    142     1604
2       February    2018    111     1526
3       March       2018    133     1636
4       April       2018    1107    3829
5       May         2018    140     1575
6       June        2018    131     1389
7       July        2018    200     893
8       August      2018    2       73
9       September   2018    1       32
10      October     2018    2       21
11      November    2018    2       21
12      December    2018    2       19

所以我的问题是 - 客户想查看在过去 12 个月内有多少服务是开放的（使用开始日期和结束日期）（不是开始了多少，而是有多少是当前的和未结束的）。这在使用当前月份时很好，但是他们也希望将前 12 个月的数据显示为滚动动态数据。

因此，例如本月 7 月，他们想查看过去 12 个月内有多少服务开放。上个月 6 月，他们想查看在 6 月之前的 12 个月中有多少服务是开放的，以此类推之前的 12 个月。

该表需要有过去 12 个月的月份名称，并在列中显示该月旁边的前 12 个月中打开的服务数量。

我希望这是有道理的，对不起，如果没有，请随时提出问题，我会尽力澄清。

输出需要看起来像当前输出表，但它目前只显示该月内启动了多少服务，这不是我们想要的。

日期表是具有不同日期格式等的参考表。如果需要，可以使用或添加它。

标签： sqlsql-serversql-server-2016

解决方案

我不得不对您的数据做出几个假设。希望我将在一分钟内显示的查询将很容易让您调整，如果其中任何一个是错误的：

  1. 我猜它的名字start_dttm是a datetimeor datetime2column。
  2. 我假设有一个名为的相应列end_dttm给出了服务的结束日期/时间，并且此列中的 null 表示服务尚未结束。
  3. 关于服务在给定月份“开放”意味着什么，我最好的猜测是它在该月内或之前的某个时间开始，并且在该月结束时还没有结束。
  4. 我从您的原始查询中假设具有相同的多个服务dim_person_id不代表不同的服务。

由于我不知道您的 中有什么date_tbl，我将展示一个不需要它的示例。考虑以下查询：

select
    BeginDate = dateadd(month, -1, dateadd(day, 1, eomonth(getdate(), -Offset.X))),
    EndDate = dateadd(day, 1, eomonth(getdate(), -Offset.X))
from
    (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) Offset(X)

这将为您提供 12 条记录，分别代表当前月份和前 11 个月中的每一个月。请注意，我的EndDate这里实际上不是该月的最后一天，而是下个月的第一天。我这样做是因为上面的假设 1；由于您的服务日期可能包含时间部分，我将通过检查它们的日期是否严格早于下个月的开始来确定它们是否属于给定月份。这是该查询给我的信息：

BeginDate   EndDate
2018-07-01  2018-08-01
2018-06-01  2018-07-01
2018-05-01  2018-06-01
2018-04-01  2018-05-01
2018-03-01  2018-04-01
2018-02-01  2018-03-01
2018-01-01  2018-02-01
2017-12-01  2018-01-01
2017-11-01  2017-12-01
2017-10-01  2017-11-01
2017-09-01  2017-10-01
2017-08-01  2017-09-01

现在，我将把上述结果集加入您的SERVICE_PROVISIONS数据中，查找每个月中具有dim_person_id > 0（来自您的原始查询）并且满足上述假设 3 的记录。

-- Some sample data (assumptions 1 & 2)
declare @SERVICE_PROVISIONS table (dim_person_id bigint, start_dttm datetime, end_dttm datetime);
insert @SERVICE_PROVISIONS values
    (1, '20180101', '20180315'),
    (1, '20180101', '20180315'),
    (2, '20171215', '20180520');

-- The CTE defines the months we'll report on, as described earlier.
with MonthsCTE as
(
    select
        BeginDate = dateadd(month, -1, dateadd(day, 1, eomonth(getdate(), -Offset.X))),
        EndDate = dateadd(day, 1, eomonth(getdate(), -Offset.X))
    from
        (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) Offset(X)
)

-- This query matches the months from the CTE against the applicable services.
select
    [Month] = datepart(month, M.BeginDate),
    [MonthName] = datename(month, M.BeginDate),
    [Year] = datepart(year, M.BeginDate),
    ServicesOpen = count(distinct S.dim_person_id)   -- Assumption 4
from
    MonthsCTE M
    left join @SERVICE_PROVISIONS S on
        S.dim_person_id > 0 and
        S.start_dttm < M.EndDate and   -- Assumption 3
        (
            S.end_dttm >= M.EndDate or
            S.end_dttm is null   -- Assumption 2
        )
group by
    M.BeginDate,
    M.EndDate
order by
    M.BeginDate;

请注意，我将子句中的 the 移到dim_person_id > 0了WHERE12JOIN个月中的每个月，即使在此期间没有打开任何服务，也仍会出现在结果集中。结果：

Month   MonthName   Year   ServicesOpen
8       August      2017   0
9       September   2017   0
10      October     2017   0
11      November    2017   0
12      December    2017   1
1       January     2018   2
2       February    2018   2
3       March       2018   1
4       April       2018   1
5       May         2018   0
6       June        2018   0
7       July        2018   0

推荐阅读