时间戳

首页 > 解决方案 > 如何按周显示每个未清项目

sql - 如何按周显示每个未清项目

问题描述

大家好，如果这不正确，对不起我的英语。

我有一个 3 列和多行的表>>

Item ID|   Opening_date | Closed_date

我需要的要求是：一年中按周分组的每个正在进行的项目。答案应该看起来像

week 1     45 ongoing item
week 2     32 ongoing item
...  
Week 37    64 ongoing item

所以我尝试了'with cte as'，但我无法得到我想要的

谢谢你的帮助最好的问候安托万

用示例编辑：

414   |  2018-01-01  |  2018-05-01
416   |  2018-01-08  |  2018-05-01
417   |  2018-01-08  |  2018-04-01

确定我使用 select datepart(ww, date) 正在进行的项目的星期是项目打开而不是关闭。有了这些值，我想要的结果是

1   |  1  
2   |  3  
3   |  3  
...
27  |  3
28  |  2

标签： sqlsql-server

解决方案

这可以通过参考涵盖数据集的适当周列表来实现。这最容易通过使用日期表来实现，但如果不可用，通过以下方式生成一个很简单cte：

declare @t table(id int, OpeningDate date, ClosingDate date);
insert into @t values
 (414,  '20180101',  '20180501')
,(416,  '20180108',  '20180501')
,(417,  '20180108',  '20180401')
;

declare @StartWeek date = (select dateadd(week,datediff(week,0,min(OpeningDate)),0) from @t);
declare @EndWeek date = (select dateadd(week,datediff(week,0,max(ClosingDate)),0) from @t);

with n(n) as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1)
    ,d(d) as (select top(datediff(week,@StartWeek,@EndWeek)+1) dateadd(week,row_number() over (order by (select null))-1,@StartWeek) from n n1,n n2,n n3,n n4,n n5)
select d.d as WeekStart
    ,count(t.id) as OpenItems
from d
    left join @t as t
        on t.OpeningDate <= d.d
            and (t.ClosingDate > d.d
                 or t.ClosingDate is null
                 )
group by d.d
order by d.d;

输出：

+---------------------+-----------+
|      WeekStart      | OpenItems |
+---------------------+-----------+
| 01.01.2018 00:00:00 |         1 |
| 08.01.2018 00:00:00 |         3 |
| 15.01.2018 00:00:00 |         3 |
| 22.01.2018 00:00:00 |         3 |
| 29.01.2018 00:00:00 |         3 |
| 05.02.2018 00:00:00 |         3 |
| 12.02.2018 00:00:00 |         3 |
| 19.02.2018 00:00:00 |         3 |
| 26.02.2018 00:00:00 |         3 |
| 05.03.2018 00:00:00 |         3 |
| 12.03.2018 00:00:00 |         3 |
| 19.03.2018 00:00:00 |         3 |
| 26.03.2018 00:00:00 |         3 |
| 02.04.2018 00:00:00 |         2 |
| 09.04.2018 00:00:00 |         2 |
| 16.04.2018 00:00:00 |         2 |
| 23.04.2018 00:00:00 |         2 |
| 30.04.2018 00:00:00 |         2 |
+---------------------+-----------+

如果您的数据集太大而无法足够快地执行，则可以使用其他稍微复杂的设计模式来提高查询效率。您可以在这里看到对各种选项的良好讨论：Aggregate for each day over time series, without using non-equijoin logic

推荐阅读