在 t-sql 中按日期范围分组 [英] grouping by date range in t-sql

问题描述

我正在尝试对该表进行查询:

I'm trying to do a query on this table:

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 月的第三行感兴趣)).

I wanted to get that result so I would know when money started to come in and when it stopped. I am also interested in the number of months before money started coming in (which explains the first row), and the number of months where money has stopped (which explains why I'm also interested in the 3rd row for July 2013 to Aug 2013).

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

I know I can use min and max on the dates and sum on amount but I can't figure out how to get the records divided that way.
Thanks!

推荐答案

这是一个想法(和 a小提琴一起去):

Here's one idea (and a fiddle to go with it):

;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) 代替它.

although I suppose as written it assumes Id are in order. You could substitute this with a ROW_NUMBER() OVER(ORDER BY startdate).

这篇关于在 t-sql 中按日期范围分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！