用于按日期范围分组的SQL报告

I have a table which stores data like this:

我有一个表存储这样的数据:

ItemID   Date   Value

01       1/1/15  1

01       2/1/15  2

01       3/1/15  0

01       4/1/15  0

01       5/1/15  3

01       6/1/15  1

How do I generate a report in SQL which would show the begin and end dates of all zero periods per item?

如何在SQL中生成一个报告,该报告将显示每个项目的所有零周期的开始和结束日期?

In this example, I would get :

在这个例子中,我会得到:

ItemID    Start    End

01        3/1/14   4/1/15

The condition is that there will be multiple zero periods during the year, and all of them should appear in the report (so simple group by will not do).

条件是一年中将有多个零时段,并且所有这些都应出现在报告中(因此简单的组不会这样做)。

Thanks very much!

非常感谢!

2 个解决方案

#1

This will return the START and END dates of all continuous zero VALUE.

这将返回所有连续零VALUE的START和END日期。

SQL Fiddle

;WITH Cte AS(
    SELECT *,
        RN = DATEADD(MONTH,- ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY [Date]), [Date])
    FROM Test
    WHERE Value = 0
)
SELECT
    ItemID,
    Start = MIN([Date]),
    [End] = MAX([Date])
FROM Cte
GROUP BY
    ItemID, RN

Sample Data

ItemID Date       Value
------ ---------- -----------
01     2015-01-01 1
01     2015-02-01 2
01     2015-03-01 0
01     2015-04-01 0
01     2015-05-01 3
01     2015-06-01 1
01     2015-07-01 0
01     2015-08-01 0
01     2015-09-01 0

RESULT

ItemID Start      End
------ ---------- ----------
01     2015-03-01 2015-04-01
01     2015-07-01 2015-09-01

#2

A more general solution (works with 2012+):

更通用的解决方案(适用于2012+):

with x as (
    select *,
    case when lag(value) over(partition by itemid order by date) <> value then 1 else 0 end as l
    from #t
),
y as (
    select *, sum(l) over(partition by itemid order by date) as grp 
    from x 
    where value = 0
)
select itemid, min(date), max(date)
from y
group by itemid, grp
order by itemid, grp

#1