SQL Server 计算一个值在多个日期范围之间出现的次数 [英] SQL Server to count how many times a value appears between multiple date ranges

问题描述

我有一个包含两列问题的表格.

I have a table with two columns in question.

• A 列 - 时间戳
• B 列 - EmpID

我想要做的是比较周数以查看 EmpID 重复了多少次.例如:

What I'm trying to do is to compare weeks to see how many times an EmpID has repeated. For example:

第 1 周是我的基准(开始日期范围，例如 BETWEEN '2017-07-22' AND '2017-07-29 23:59:59.993').我现在想将第 2 周与第 1 周进行比较.如果 EmpID 在第 2 周重复出现，我将看到计数为 2，如果它在第 2 周第一次出现，则计数为 1.

Week1 is my base (starting date range e.g. BETWEEN '2017-07-22' AND '2017-07-29 23:59:59.993'). I now want to compare week2 against week1. If an EmpID repeats in week2 I was to see a count of 2 and if it appears for the first time in week2 then a count of 1.

继续进行第 3 周.如果 EmpID 出现在第 1 周、第 2 周和第 3 周，那么我想看到计数为 3，如果它只出现在第 2 周和第 3 周，则计数为 2，如果仅出现在第 3 周，则计数为 1.

Moving on to week3. If the EmpID appears in week1, week2 and week3 then I want to see a count of 3, if it only appears in week2 and week3 then a count of 2 and if it only appears in week3 then a count of 1.

最后是第 4 周.如果 EmpID 出现在第 1 周、第 2 周、第 3 周和第 4 周，那么我希望看到计数为 4.如果 EmpID 出现在第 2 周、第 3 周和第 4 周，则计数为 3，如果出现在第 3 周和第 4 周，则计数为 2如果它仅在第 4 周第一次出现，则计数为 1.

And finally for week4. If the EmpID appears in week1, week2, week3 and week4 then I want to see a count of 4. If the EmpID appears in week2, week3 and week4 then a count of 3, if it appears in week3 and week4 then a count of 2 and if it only appears for the first time in week4 then a count of 1.

任何帮助将不胜感激.

添加了我迄今为止尝试过但未获得预期结果的内容.

Added what I've tried so far but not getting the desired results.

select t.emp_id,
(select count(emp_id) 
from [Vacation Audit Care 2017]
WHERE ((UPLOAD_DATE BETWEEN '2017-07-22' AND '2017-07-29 23:59:59.993') or(UPLOAD_DATE BETWEEN '2017-07-29' AND '2017-08-05 23:59:59.993') or (UPLOAD_DATE BETWEEN '2017-08-12' AND '2017-08-19 23:59:59.993'))
and emp_id=t.emp_id) as counts
from [Vacation Audit Care 2017] t
group by t.emp_id
order by counts desc

样本数据

╔═════════════════╦═════════╗
║ Time_Stamp      ║ Emp_ID  ║
║ 7/20/2017 19:40 ║ 3140340 ║
║ 7/20/2017 19:40 ║ 2000950 ║
║ 7/20/2017 19:40 ║ 3118410 ║
║ 7/20/2017 19:40 ║ 311840  ║
║ 7/23/2017 21:19 ║ 3140340 ║
║ 7/23/2017 21:19 ║ 2000950 ║
║ 7/23/2017 21:19 ║ 3118410 ║
║ 7/23/2017 21:19 ║ 3124160 ║
║ 7/30/2017 7:00  ║ 3140340 ║
║ 7/30/2017 7:00  ║ 2000950 ║
║ 7/30/2017 7:00  ║ 3118410 ║
║ 7/30/2017 7:00  ║ 311840  ║
║ 8/6/2017 12:00  ║ 3140340 ║
║ 8/6/2017 12:00  ║ 3118410 ║
║ 8/6/2017 12:00  ║ 3124160 ║
║ 8/6/2017 12:00  ║ 311840  ║
║ 8/13/2017 12:00 ║ 3140340 ║
║ 8/13/2017 12:00 ║ 3118410 ║
║ 8/13/2017 12:00 ║ 3124160 ║
║ 8/13/2017 12:00 ║ 311840  ║
╚═════════════════╩═════════╝

预期输出

╔═════════╦════════╦═══════╦════════╗
║         ║ 30-Jul ║ 6-Aug ║ 13-Aug ║
║ emp_id  ║ wk2    ║ wk3   ║ wk4    ║
║ 3140340 ║ 2      ║ 3     ║ 4      ║
║ 2000950 ║ 2      ║       ║        ║
║ 3118410 ║ 2      ║ 3     ║ 4      ║
║ 311840  ║ 1      ║ 2     ║ 3      ║
║ 3124160 ║        ║ 1     ║ 2      ║
╚═════════╩════════╩═══════╩════════╝

根据预期输出，使用 7/23 作为我的基础(第 1 周)下表显示了预期的数据结果.在第 2 周 311840 首次出现，所以我预计计数为 1.

As per the expected output using 7/23 as my base (week1) the table shows the expected data results. In week2 311840 appears for the first time so I expect a count of 1.

接下来的一周 (8/6) 311840 在 wk2 中出现一次，在 wk3 中出现一次，因此我预计计数为 2，而 3124160 首次出现，因此我预计计数为 1 等.

The following week (8/6) 311840 appears once in wk2 and once in wk3 so I expect a count of 2 while 3124160 appears for the first time so I expect a count of 1 etc.

推荐答案

这将以您在示例中显示的格式给出结果.我在输出之前和之后包括了一个额外的一周.要删除这些额外的周，只需从枢轴中删除 [29] 和 [33].注释掉枢轴线以获取基础数据.

This will give results in the format that you show in your sample. I've included an extra week before and after in the output. To remove those extra weeks just remove [29] and [33] from the pivot. Comment out the pivot line to just get the underlying data.

;WITH 
weekcounts AS (
    SELECT Time_Stamp, Emp_ID, DATEPART(week, Time_Stamp) AS int_week FROM sampleData
)

,counts AS (
    SELECT Emp_ID, int_week, 1 AS int_count
    FROM weekcounts
    UNION ALL
    SELECT weekcounts.Emp_ID, weekcounts.int_week, 1 AS int_count
    FROM weekcounts
        INNER JOIN counts
            ON weekcounts.Emp_ID = counts.Emp_ID
            AND (weekcounts.int_week - 1) = counts.int_week
)

,countsagg AS (
    SELECT Emp_ID, int_week, SUM(int_count) AS int_count
    FROM counts
    GROUP BY Emp_ID, int_week
)

SELECT * FROM countsagg
PIVOT (MAX(int_count) FOR int_week IN ([29],[30],[31],[32],[33])) piv

这篇关于SQL Server 计算一个值在多个日期范围之间出现的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！