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!
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
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)
.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With