按日期范围分组在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

I want to get the output:

我想获得输出:

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

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).

我想得到那个结果,所以我会知道什么时候开始进货以及什么时候停止。我也对钱开始进入之前的几个月感兴趣(这解释了第一行),以及资金停止的月数(这也解释了为什么我也对2013年7月到2013年8月的第3行感兴趣)。

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!

我知道我可以在日期上使用最小值和最大值并总结金额,但我无法弄清楚如何将记录划分为这种方式。谢谢!

6 个解决方案

#1

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)

And a second, without using UNION (fiddle):

第二,不使用UNION(小提琴):

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

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

虽然我认为如上所述,它假定Id是有序的。您可以使用ROW_NUMBER()OVER(ORDER BY startdate)替换它。

#2

with CT as
(
    select t1.*,
           ( select max(endDate) 
             from t 
             where startDate<t1.StartDate and SIGN(amount)<>SIGN(t1.Amount)
           ) as GroupDate
    from t  as t1
) 
select min(StartDate) as StartDate,
       max(EndDate) as EndDate,
       sum(Amount) as Amount
from CT
group by GroupDate
order by StartDate

SQLFiddle demo

#3

Something like that should do it :

这样的事情应该这样做:

select min(startdate), max(enddate), sum(amount) from paiements
   where enddate   < (select min(startdate) from paiements where amount >0)
union
select min(startdate), max(enddate), sum(amount) from paiements
   where startdate >= (select min(startdate) from paiements where amount >0)
     and enddate   <= (select max(enddate) from paiements where amount >0)
union
select min(startdate), max(enddate), sum(amount) from paiements
   where startdate > (select max(enddate) from paiements where amount >0)

But for this kind of reporting, It's probably more explicit using multiple queries.

但对于这种报告,使用多个查询可能更明确。

#4

This does what you want:

这样做你想要的:

-- determine the three periods
DECLARE @StartMoneyIn INT
DECLARE @EndMoneyIn INT

SELECT @StartMoneyIn = MIN(Id)
FROM [Amounts]
WHERE amount > 0

SELECT @EndMoneyIn = MAX(Id)
FROM [Amounts]
WHERE amount > 0

-- retrieve the amounts
SELECT MIN(startdate) AS startdate, MAX(enddate) AS enddate, SUM(amount) AS amount
FROM [Amounts]
WHERE Id < @StartMoneyIn
UNION
SELECT MIN(startdate), MAX(enddate), SUM(amount)
FROM [Amounts]
WHERE Id >= @StartMoneyIn AND Id <= @EndMoneyIn
UNION
SELECT MIN(startdate), MAX(enddate), SUM(amount)
FROM [Amounts]
WHERE Id > @EndMoneyIn

#5

If all you want to do is to see when money started coming in and when it stopped, this might work for you:

如果你想要做的就是看钱什么时候开始进入以及什么时候停止,这可能对你有用:

select 
    min(startdate),
    max(enddate),
    sum(amount)
where
    amount > 0

This would not include the periods where there was no money coming in though.

这不包括没有钱进入的时期。

#6

If you don't care about the total in the period, but only want the records where you go from 0 to something and vica versa, you could do something crazy like this:

如果您不关心期间的总数,但只想要从0到某事的记录,反之亦然,您可以做一些像这样的疯狂事情:

select *
from MoneyTable mt
where exists ( select *
               from MoneyTable mtTemp
               where mtTemp.enddate = dateadd(day, -1, mt.startDate)
               and mtTemp.amount <> mt.amount
               and mtTemp.amount * mt.amount = 0)

Or if you must include the first record:

或者,如果您必须包含第一条记录:

select *
from MoneyTable mt
where exists ( select *
               from MoneyTable mtTemp
               where mtTemp.enddate = dateadd(day, -1, mt.startDate)
               and mtTemp.amount <> mt.amount
               and mtTemp.amount * mt.amount = 0 )
or not exists ( select *
                from MoneyTable mtTemp
                where mtTemp.enddate = dateadd(day, -1, mt.startDate))

Sql Fiddle

#1