时间戳

首页 > 解决方案 > 我需要使用创建日期获取前 5 天和 6-10 天的数据

sql - 我需要使用创建日期获取前 5 天和 6-10 天的数据

问题描述

如标题所示，我需要获取前 5 天、6-10 天的数据。我有 3 个参数：开始日期、结束日期、用户

我有创建日期的数据如下

Createdate
----------------

2019-11-01
2019-11-04
2019-11-05
2019-11-10
2019-11-21
2019-11-30

因此，正如您在上面看到的，我们在前 5 天有 3 个日期，在 6-10 天有 1 个日期。

我用下面的查询

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 天。请任何一位帮助

标签： sqlsql-serverstored-proceduresreporting-servicesssrs-2012

解决方案

如果我理解正确，下一条语句可能会有所帮助：

桌子：

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

推荐阅读