时间戳

首页 > 解决方案 > 统计过去 12 个月内提交过罚单的所有人数 每月分组

sql - 统计过去 12 个月内提交过罚单的所有人数 每月分组

问题描述

我有这些示例表：

CREATE TABLE #tmp_persons (
    PersonID int primary key identity
    , FirstName varchar(max)
    , LastName varchar(max)
)

CREATE TABLE #tmp_tickets (
    TicketNum int primary key identity
    , Title varchar(max)
    , Descr varchar(max)
    , DateFiled datetime
    , SubmittedBy int FOREIGN KEY REFERENCES #tmp_persons(PersonID)
)

CREATE TABLE #tmp_assigned_lead (
    AssignmentID int primary key identity
    , PersonID int FOREIGN KEY REFERENCES #tmp_persons(PersonID) -- ID of the Lead
    , AssignedPersonID int FOREIGN KEY REFERENCES #tmp_persons(PersonID) -- ID of the person assigned
)

-- DATA DUMP
INSERT INTO #tmp_persons
VALUES
(1, 'John', 'Doe'), (2, 'Jane', 'Doe'),
(3, 'Carl', 'Smith'), (4, 'Jenny', 'Smith'),
(5, 'John', 'Wick'), (6, 'Stephanie', 'Mathews')

INSERT INTO #tmp_assigned_lead (PersonID, AssignedPersonID)
VALUES
(1, 2), (1, 3), (1, 4), (1, 5), (1, 6)

INSERT INTO #tmp_tickets (Title, Descr, DateFiled, SubmittedBy)
VALUES
('Ticket 1', 'blah', '01-01-2019', 2),
('Ticket 2', 'blah', '01-02-2019', 2),
('Ticket 3', 'blah', '05-01-2019', 4),
('Ticket 4', 'blah', '05-01-2019', 2),
('Ticket 5', 'blah', '06-01-2019', 5),
('Ticket 6', 'blah', '07-01-2019', 6),--
('Ticket 7', 'blah', '4-17-2018', 6),
('Ticket 8', 'blah', '12-30-2018', 6),
('Ticket 9', 'blah', '1-28-2019', 3),
('Ticket 10', 'blah', '3-16-2019', 3),
('Ticket 11', 'blah', '3-30-2018', 5),
('Ticket 12', 'blah', '2-4-2018', 4),
('Ticket 13', 'blah', '1-11-2019', 6),
('Ticket 14', 'blah', '9-5-2018', 6)

所以这里有几点：

Contributor现在我的要求是，我想统计每个月过渡到领导下的所有人。这将是每月的记录。

示例场景：

  1. Person1已注册Jan 01, 2018并且从那以后就再也没有提交过罚单，直到Jan 01, 2019他提交了他的第一张罚单。这将仅适用Person1于Contributor2019 年 1 月，但他Jan 2018是Dec 2018.
  2. Person1提交了另一张罚单Feb 2019。虽然他提交了新的票，但他仍然没有被计入Feb 2019成为成员的记录，Contributor因为他已经Contributor是Jan 2019
  3. Person2已注册Feb 23, 2019并立即提交了罚单。这使他/她成为 1Contributor月份“成为贡献者的成员”的计数Feb 2019。

到目前为止，我提出的这个查询将每人和每月的票分组：

SELECT
    SubmittedBy
    , MaxDate = MAX(DateFiled)
    , PeriodMonth = MONTH(DateFiled)
    , PeriodYear = YEAR(DateFiled)
FROM #tmp_tickets
GROUP BY
    SubmittedBy
    , MONTH(DateFiled)
    , YEAR(DateFiled)

这就是我遇到的障碍。我想不出一个查询将返回一个潜在客户下的成员数，谁成为Contributors.

这就是我到目前为止所拥有的。我将上述查询用作CTE：

;with cte as (
    SELECT
        SubmittedBy
        , MaxDate = MAX(DateFiled)
        , PeriodMonth = MONTH(DateFiled)
        , PeriodYear = YEAR(DateFiled)
    FROM #tmp_tickets
    GROUP BY
        SubmittedBy
        , MONTH(DateFiled)
        , YEAR(DateFiled)
)
SELECT
    lead.PersonID
    , ContributorsCnt = COUNT(1)
    , PeriodMonth
    , PeriodYear
FROM #tmp_assigned_lead lead
INNER JOIN cte
    ON lead.AssignedPersonID = cte.SubmittedBy
-- WHERE?? 
-- check if member has no tickets in the past 12 months
GROUP BY
    lead.PersonID
    , PeriodMonth
    , PeriodYear

这里的每个数据样本，这就是cte上面的记录： cte

编辑：

添加了数据转储和分析点。

要点：

  1. 2018 年 2 月，PersonID 4 成为贡献者。除了他，没有人提出过罚单。这会给我们一行：

    LeadID: 1, PeriodYear: 2018, PeriodMonth: 2, Contributor: 1

  2. 在 2018 年 3 月，PersonID 5 成为了贡献者。同样，这将为我们提供另一行：

    LeadID: 1, PeriodYear: 2018, PeriodMonth: 3, Contributor: 1

  3. 2018 年 4 月，PersonID 6 成为贡献者：

    LeadID: 1, PeriodYear: 2018, PeriodMonth: 4, Contributor: 1

  4. 从 2018-05 到 2019-01，没有其他人成为贡献者。此处的 PersonID 6 不计算在内，因为他仍然是贡献者。

  5. 在 2019-01，PersonID 2 和 3 成为贡献者。这是他们第一次提交罚单。同样，PersonID 6 仍然是贡献者。这会给我们一行：

    LeadID: 1, PeriodYear: 2019, PeriodMonth: 1, Contributor: 2

  6. 2019 年 2 月，PersonID 4 重新成为普通会员，而不是贡献者，因为已经 12 个月了，从那以后他没有提交任何票。为此不需要行。

  7. 在 2019 年 3 月，PersonID 5 成为普通成员（与第 6 点相同的逻辑）。第 3 个人提交了一张票，但他仍然是贡献者。总的来说，不需要行。

  8. 在 2019 年 5 月，第 2 个人提交了一张票，但他仍然是贡献者。第 4 个人提交了工单，他已经是普通会员，所以他现在是贡献者：

    LeadID: 1, PeriodYear: 2019, PeriodMonth: 5, Contributor: 1

  9. 在 2019-06，Person 5 成为贡献者：

    LeadID: 1, PeriodYear: 2018, PeriodMonth: 5 Contributor: 1

  10. 在 2019 年 7 月，第 6 个人提交了一张票，但自从他的最后一张票于 2019 年 1 月提交后，他仍然是贡献者。（真是个大忙人！）

预期数据集：

LeadID: 1, PeriodYear: 2018, PeriodMonth: 2, Contributor: 1
LeadID: 1, PeriodYear: 2018, PeriodMonth: 3, Contributor: 1
LeadID: 1, PeriodYear: 2018, PeriodMonth: 4, Contributor: 1
LeadID: 1, PeriodYear: 2019, PeriodMonth: 1, Contributor: 2
LeadID: 1, PeriodYear: 2019, PeriodMonth: 5, Contributor: 1
LeadID: 1, PeriodYear: 2018, PeriodMonth: 5 Contributor: 1

我希望这有助于澄清我的问题。

标签： sqlsql-servertsql

解决方案

创建一个获取过去 12 个月提交票证列表的函数

ALTER FUNCTION func_isContributor(@dateFiled as DATE, @PersonID as INTEGER)
RETURNS  INTEGER
AS
BEGIN
    return 
        (SELECT
            count(*)
        FROM
            tmp_tickets
        WHERE 
            DateFiled BETWEEN  DATEADD(month, -12, @dateFiled) and  @dateFiled AND SubmittedBy = @PersonID)
END

然后检查此人是否有过去 12 个月的记录

; with cte as (
    SELECT 
        SubmittedBy,
        DateFiled,
        isCounted = case when dbo.func_isContributor(DateFiled,SubmittedBy) -1 = 0 then 1 else 0 END
    FROM
        tmp_tickets
)
SELECT
    lead.PersonID,
        ContributorCount = Count(1),
        PeriodMonth = MONTH(DateFiled),
        PeriodYear = YEAR(DateFiled)
FROM tmp_assigned_lead lead
INNER JOIN cte
    ON lead.AssignedPersonID = cte.SubmittedBy and cte.isCounted = 1
GROUP BY 
    lead.PersonID,
    MONTH(DateFiled),
    YEAR(DateFiled)

推荐阅读