MSSQL Bepalen waar cum. aantal door de nul gaat

[Henk6a]

Ik heb een MSSQL tabel met datums en aantallen

13-01-2012 200
15-01-2012 300
17-01-2012 1200
18-01-2012 -1500
21-01-2012 500
23-01-2012 -1500
25-01-2012 500


Ik zoek nu dus de datum waar het cumulatieve som van de aantallen van positief naar negatief gaat en/of andersom.

Het liefst zou ik dit mbv een sql query oplossen

Heeft iemand een idee hoe dit op te lossen?

[evert]

SELECT DTDV.Vorige_datum, CumAantalV, DTDV.datum, CumAantal FROM
(SELECT a1.datum, Max(a2.datum) AS Vorige_datum
FROM AantalPerDatum AS a1, AantalPerDatum AS a2
WHERE a2.datum < a1.datum
GROUP BY a1.datum) DTDV,
(Select a1.datum, sum(a2.aantal) as CumAantal
from AantalPerDatum a1, AantalPerDatum a2
where a2.datum <= a1.datum
group by a1.datum) CUM1,
(Select a1.datum, sum(a2.aantal) as CumAantalV
from AantalPerDatum a1, AantalPerDatum a2
where a2.datum <= a1.datum
group by a1.datum) CUM2
WHERE DTDV.datum = CUM1.datum AND DTDV.Vorige_Datum = CUM2.datum
AND ((CumAantal > 0 AND CumAantalV <= 0) Or (CumAantal <= 0 AND CumAantalV > 0))

[chamers]

Vanaf de SQL/2003 standaard zou je dit met een WINDOW clause op kunnen lossen (getest mbv Sybase SQL AnyWhere 11)

Hiervoor heb ik de volgende test tabel aangemaakt:

create table test (
datum date,
aantal integer
);

insert into test (datum, aantal) values ('2012-01-13', 200);
insert into test (datum, aantal) values ('2012-01-15', 300);
insert into test (datum, aantal) values ('2012-01-17', 1200);
insert into test (datum, aantal) values ('2012-01-18', -1500);
insert into test (datum, aantal) values ('2012-01-21', 500);
insert into test (datum, aantal) values ('2012-01-23', -1500);
insert into test (datum, aantal) values ('2012-01-25', 500);

En als ik het goed begrijp wil je dus de Sum hebben van het aantal t/m vandaag:

select datum, 
       Sum(aantal) over (rows between unbounded preceding and current row) as TotaalTotEnMetDatum
  from test
 order by datum asc;

Result:
13-01-2012 200
15-01-2012 500
17-01-2012 1700
18-01-2012 200
21-01-2012 700
23-01-2012 -800
25-01-2012 -300

En dan kun je dus vervolgens op deze manier bepalen op welke datum dat aantal voor het eerst < 0 wordt:

select top 1 datum
  from (select datum, 
               Sum(aantal) over (rows between unbounded preceding and current row) as TotaalTotEnMetDatum
          from test
         order by datum) as tempResult
 where TotaalTotEnMetDatum < 0
 order by datum asc;

[Henk6a]

@evert krijg het nog niet voor elkaar, geeft bij mij geen resultaten. Misschien doe ik nog iets fout.

@chamers Helaas werkt dit niet op MSSQL 2008


Ik zit er over te denken om te upgraden naar MSSQL 2012. Deze kent nl de over constructie

[evert]

Origineel gepost door Henk6a

@evert krijg het nog niet voor elkaar, geeft bij mij geen resultaten. Misschien doe ik nog iets fout.

Geeft hij dan een foutmelding?

[Henk6a]

Nee, maar ik kreeg geen resultaten, terwijl die er wel zouden moeten zijn

heb inmiddels sql2012 geinstalleerd en ga daar nu eerst mee stoeien

[chamers]

Zie trouwens dat ik nog een foutje had zitten in m'n voorbeeld.
De order by moet je al zetten in de OVER clause.

select top 1 datum
  from (select datum, 
               Sum(aantal) over (order by datum rows between unbounded preceding and current row) as TotaalTotEnMetDatum
          from test) as tempResult
 where TotaalTotEnMetDatum < 0
 order by datum asc;

[evert]

Origineel gepost door Henk6a

Nee, maar ik kreeg geen resultaten, terwijl die er wel zouden moeten zijn

Ik heb het in MSAccess uitgevogeld. Zie bijlage en screenshot.

[Henk6a]

Dit is het geworden:

SELECT MAX(TR.TradeDate)
FROM (SELECT Tradedate,Quantity,
SUM(Quantity) OVER (ORDER BY Tradedate ROWS 1 PRECEDING ) AS Cumvorige,
SUM(Quantity) OVER (ORDER BY Tradedate) AS Cumaantal
FROM transacties) TR
WHERE (TR.CumVorige <= 0 AND TR.CumAantal >= 0) OR (TR.CumVorige >= 0 AND TR.CumAantal <= 0)