在T-SQL中按日期范围分组

Question

我正在尝试对此表进行查询：

Id       startdate     enddate       amount
1        2013-01-01   2013-01-31      0.00
2        2013-02-01   2013-02-28      0.00
3        2013-03-01   2013-03-31      245
4        2013-04-01   2013-04-30      529
5        2013-05-01   2013-05-31      0.00
6        2013-06-01   2013-06-30      383
7        2013-07-01   2013-07-31      0.00
8        2013-08-01   2013-08-31      0.00

我想获得输出：

2013-01-01          2013-02-28          0
2013-03-01          2013-06-30          1157
2013-07-01          2013-08-31           0

我想得到那个结果，所以我会知道什么时候开始赚钱，什么时候停止钱。我还对开始花钱之前的月份数（这解释了第一行）以及停止钱的月份数（这解释了为什么我也对2013年7月至2013年8月的第三行感兴趣）感兴趣。
）。

我知道我可以在日期上使用min和max，在金额上使用sum，但是我不知道如何以这种方式划分记录。
谢谢！

Answer 1

这是一个主意（和它有关）：

;WITH MoneyComingIn AS
(
    SELECT MIN(startdate) AS startdate, MAX(enddate) AS enddate, 
        SUM(amount) AS amount
    FROM myTable
    WHERE amount > 0
)
SELECT MIN(startdate) AS startdate, MAX(enddate) AS enddate, 
    SUM(amount) AS amount
FROM myTable
WHERE enddate < (SELECT startdate FROM MoneyComingIn)
UNION ALL
SELECT startdate, enddate, amount
FROM MoneyComingIn
UNION ALL
SELECT MIN(startdate) AS startdate, MAX(enddate) AS enddate, 
    SUM(amount) AS amount
FROM myTable
WHERE startdate > (SELECT enddate FROM MoneyComingIn)

第二秒，不使用UNION（fiddle）：

SELECT MIN(startdate), MAX(enddate), SUM(amount)
FROM
(
    SELECT startdate, enddate, amount,
    CASE 
        WHEN EXISTS(SELECT 1 
                    FROM myTable b 
                    WHERE b.id>=a.id AND b.amount > 0) THEN
            CASE WHEN EXISTS(SELECT 1 
                             FROM myTable b 
                             WHERE b.id<=a.id AND b.amount > 0) 
                 THEN 2 
                 ELSE 1 
            END
        ELSE 3
    END AS partition_no
    FROM myTable a
) x
GROUP BY partition_no

尽管我认为如所写，但它假定Id是有序的。您可以用代替它ROW_NUMBER() OVER(ORDER BY startdate)。