Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

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!

like image 239
Dean Avatar asked Jul 30 '13 12:07

Dean


3 Answers

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

like image 132
valex Avatar answered Sep 25 '22 09:09

valex


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

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

like image 40
lc. Avatar answered Sep 21 '22 09:09

lc.


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.

like image 37
Nicolas Avatar answered Sep 24 '22 09:09

Nicolas