我需要使用创建日期获取前5天和6-10天的数据
我需要使用创建日期获取前5天和6-10天的数据
提问于 2020-01-02 13:49:17
如标题所示，我需要获取前5天，6-10天的数据。我有3个参数:开始日期、结束日期、用户
我有如下创建日期的数据
Createdate
----------------
2019-11-01
2019-11-04
2019-11-05
2019-11-10
2019-11-21
2019-11-30正如你在上面看到的，我们有3个日期在前5天内，1个日期在6-10天内。
我使用了下面的查询
Select count(date)
from Data
Where cast(cdate as date) between cast(cdate as date)
and dateadd(day,5,cdate) Group by cdate
Expected results
-------------
Date First 5 6-10
---- ----- ----
2019-11-01 1
2019-11-04 1
2019-11-05 1
2019-11-10 0 1但是我得到了所有的数据，而不是前5天中只有3天。请大家帮帮忙
回答 2
Stack Overflow用户
发布于 2020-01-02 14:02:54
Stack Overflow用户
发布于 2020-01-02 15:00:18
如果我没理解错的话，下面这句话可能会有帮助：
表：
CREATE TABLE Data (
CreateDate date
)
INSERT INTO Data
(CreateDate)
VALUES
('20191101'),
('20191104'),
('20191105'),
('20191110'),
('20191121'),
('20191130')声明：
SELECT
CreateDate,
CASE WHEN DATEDIFF(day, FIRST_VALUE(CreateDate) OVER (ORDER BY CreateDate ASC), CreateDate) / 5 = 0 THEN 1 END AS [First 5 Days],
CASE WHEN DATEDIFF(day, FIRST_VALUE(CreateDate) OVER (ORDER BY CreateDate ASC), CreateDate) / 5 = 1 THEN 1 END AS [6-10 Days],
CASE WHEN DATEDIFF(day, FIRST_VALUE(CreateDate) OVER (ORDER BY CreateDate ASC), CreateDate) / 5 > 1 THEN 1 END AS [More Days]
FROM Data 结果：
CreateDate First 5 Days 6-10 Days More Days
2019-11-01 1 NULL NULL
2019-11-04 1 NULL NULL
2019-11-05 1 NULL NULL
2019-11-10 NULL 1 NULL
2019-11-21 NULL NULL 1
2019-11-30 NULL NULL 1如果有重复日期的行，请使用下一条语句：
SELECT
CreateDate,
SUM([First 5 Days]) AS [First 5 Days],
SUM([6-10 Days]) AS [6-10 Days],
SUM([More Days]) AS [More Days]
FROM (
SELECT
CreateDate,
CASE WHEN DATEDIFF(day, FIRST_VALUE(CreateDate) OVER (ORDER BY CreateDate ASC), CreateDate) / 5 = 0 THEN 1 END AS [First 5 Days],
CASE WHEN DATEDIFF(day, FIRST_VALUE(CreateDate) OVER (ORDER BY CreateDate ASC), CreateDate) / 5 = 1 THEN 1 END AS [6-10 Days],
CASE WHEN DATEDIFF(day, FIRST_VALUE(CreateDate) OVER (ORDER BY CreateDate ASC), CreateDate) / 5 > 1 THEN 1 END AS [More Days]
FROM Data
) t
GROUP BY CreateDate页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59558814
复制相关文章
相似问题
腾讯云开发者
