[MSSQL] Start en Einddatum in een range

[Verwijderd]

Beste Mensen,

Ik heb een tabel met polissen deze hebben een startdatum, einddatum en een maandbedrag.
Ik moet een query bouwen waarbij de eindgebruiker een datum range aangeeft (1-1-2009 t/m 1-11-2009. Vervolgens moeten de totalen maandbedragen per maand worden weergegeven. Rekening gehouden dat een polis in deze reeks maanden gestart of beëindigd kan worden.

Ik heb me bedacht dat ik in het bovenste voorbeeld 11 keer een query moet uitvoeren (per maand). Ik denk dat er een betere manier voor bestaat maar heb deze nog niet gevonden. Ik hoop dat jullie me in de juiste richting kunnen leiden.

[whoami]

Check eens de aggregate functie SUM en de GROUP BY clausule.
Check ook de MONTH functie.

[glmona]

En ook de YEAR functie, mocht de periode over meerdere jaren gaan..

[NMe]

glmona schreef op dinsdag 03 november 2009 @ 10:28:
En ook de YEAR functie, mocht de periode over meerdere jaren gaan..

Niet eens zozeer dat, maar vooral ook als er data van meerdere jaren in de database staat.

Overigens kun je bij meer arbitraire ranges (15 juni tot en met 4 augstus ofzo ) ook BETWEEN gebruiken, maar dat zou het groupen per maand weer iets lastiger maken.

[Cartman!]

Hoezo is groeperen lastiger? Je kunt in je view toch gewoon een nieuwe list beginnen als de maand is veranderd tov. de vorige row? En de bedragen kun je in een array oid bijhouden om onderaan de totalen te laten zien. Of begrijp ik niet helemaal wat de TS wil?

[NMe]

Cartman! schreef op dinsdag 03 november 2009 @ 11:44:
Hoezo is groeperen lastiger? Je kunt in je view toch gewoon een nieuwe list beginnen als de maand is veranderd tov. de vorige row? En de bedragen kun je in een array oid bijhouden om onderaan de totalen te laten zien. Of begrijp ik niet helemaal wat de TS wil?

Groupen gaat in de query al hoor, heb je geen code voor nodig in je view.

SQL:

SELECT SUM(...), MONTH(datum) FROM tabel WHERE YEAR(datum) = 2009 GROUP BY MONTH(datum)

Mijn punt was meer dat je met arbitraire datums waarschijnlijk gelijke periodes wil laten zien in plaats van aantallen per maand, maar da's niet zozeer relevant voor de topicstarter en meer bedoeld als kanttekening voor andere gebruikers die het lezen.

[ Voor 18% gewijzigd door NMe op 03-11-2009 11:50 ]

[Woy]

NMe schreef op dinsdag 03 november 2009 @ 11:47:
[...]
Mijn punt was meer dat je met arbitraire datums waarschijnlijk gelijke periodes wil laten zien in plaats van aantallen per maand, maar da's niet zozeer relevant voor de topicstarter en meer bedoeld als kanttekening voor andere gebruikers die het lezen.

Als je het hebt over "versprongen" maanden ( dus van 5-10 tot 5-11 ipv 1-10 tot 1-11 ) dan kun je dat ook redelijk eenvoudig doen met een datediff waarschijnlijk

[Cartman!]

NMe schreef op dinsdag 03 november 2009 @ 11:47:
[...]

Groupen gaat in de query al hoor, heb je geen code voor nodig in je view.

SQL:

SELECT SUM(...), MONTH(datum) FROM tabel WHERE YEAR(datum) = 2009 GROUP BY MONTH(datum)

Mijn punt was meer dat je met arbitraire datums waarschijnlijk gelijke periodes wil laten zien in plaats van aantallen per maand, maar da's niet zozeer relevant voor de topicstarter en meer bedoeld als kanttekening voor andere gebruikers die het lezen.

Ik denk dat ik dan iets anders bedoel dan jij. Ik bedoel alle rows ophalen tussen 2 data met BETWEEN en daarna in je view een scheiding maken tussen de maanden, layout-technisch dus om een scheiding te maken tussen de maanden.

[NMe]

Cartman! schreef op dinsdag 03 november 2009 @ 13:15:
[...]

Ik denk dat ik dan iets anders bedoel dan jij. Ik bedoel alle rows ophalen tussen 2 data met BETWEEN en daarna in je view een scheiding maken tussen de maanden, layout-technisch dus om een scheiding te maken tussen de maanden.

SQL:

SELECT SUM(...), MONTH(datum) FROM tabel WHERE datum BETWEEN '20090101' AND '20091231' GROUP BY MONTH(datum)

Je database heeft dan die scheiding tussen de maanden al gemaakt.

[Woy]

NMe schreef op dinsdag 03 november 2009 @ 13:24:
[...]

SQL:

SELECT SUM(...), MONTH(datum) FROM tabel WHERE datum BETWEEN '20090101' AND '20091231' GROUP BY MONTH(datum)

Je database heeft dan die scheiding tussen de maanden al gemaakt.

offtopic:
En je mist de data van 31 december

[NMe]

Details.

[Macros]

Niet, BETWEEN is INCLUSIVE in SQL Server.
MS raadt between bij data af, vooral als je timestamps / datetime velden gebruikt. Juist omdat het inclusive is. Je kan het beter zo doen: date >= 31122005 and date < 01012006

[Woy]

Macros schreef op dinsdag 03 november 2009 @ 13:32:
Niet, BETWEEN is INCLUSIVE in SQL Server.
MS raadt between bij data af, vooral als je timestamps / datetime velden gebruikt. Juist omdat het inclusive is. Je kan het beter zo doen: date >= 31122005 and date < 01012006

als je een DateTime met between queryd dan worden de between parameters toch gewoon naar een DateTime omgezet? Dat zou betekenen dat '2009-12-31' automatisch gevonverteerd word naar de datum 2009-12-31 00:00 en dus valt alles vanaf 2009-12-31 00:01 er al af.

Ik zal het zo eens even testen

[Cartman!]

NMe schreef op dinsdag 03 november 2009 @ 13:24:
[...]

SQL:

SELECT SUM(...), MONTH(datum) FROM tabel WHERE datum BETWEEN '20090101' AND '20091231' GROUP BY MONTH(datum)

Je database heeft dan die scheiding tussen de maanden al gemaakt.

Nog steeds niet wat ik bedoel, laat maar dan

[Woy]

Het is inderdaad niet inclusive op dag nivo.

SQL:

select * from dbo.Test

Resultaat

2009-01-01 00:00:00.000
2009-01-02 00:00:00.000
2009-12-31 00:00:00.000
2009-12-31 01:00:00.000

SQL:

select * from dbo.Test
where SomeDate between '2009-1-1' and '2009-12-31'

Resultaat:

2009-01-01 00:00:00.000
2009-01-02 00:00:00.000
2009-12-31 00:00:00.000

[NMe]

Klopt volgens mij wel ja, Woy. Between is dus best te gebruiken, mits je ook maar een tijd opgeeft.

[Woy]

NMe schreef op dinsdag 03 november 2009 @ 13:45:
Klopt volgens mij wel ja, Woy. Between is dus best te gebruiken, mits je ook maar een tijd opgeeft.

Of accepteren dat een actie middernacht nog bij de vorige dag/maand meetelt

[Macros]

Woy schreef op dinsdag 03 november 2009 @ 13:48:
[...]

Of accepteren dat een actie middernacht nog bij de vorige dag/maand meetelt

Je moet er wel erg mee oppassen hoor. Ik zou between gewoon niet gebruiken in datetime's. Want naast dit issue kan een datetime (datetime2 wel) niet zomaar alle times aan. Als je bijvoorbeeld het hele jaar wilt hebben in een between, dan moet je doen:
between 2006-01-01T00:00:00.000 and 2006-12-31T23:59:59.999
Maar 2006-12-31T23:59:59.999 past niet in een datetime, de laatste die er wel in past is 2006-12-31T23:59:59.997. Dus 2006-12-31T23:59:59.999 wordt naar 2007-01-01T00:00:00.000 afgerond.
Dus dan moet je eigenlijk doen:
between 2006-01-01T00:00:00.000 and 2006-12-31T23:59:59.997
wat natuurlijk een beetje vreemd is.
Of je gebruikt gewoon
date >= 2006-01-01T00:00:00.000 and date < 2007-01-01T00:00:00.000
En dan kan je de tijden weglaten:
date >= 2006-01-01 and date < 2007-01-01

[Woy]

Macros schreef op dinsdag 03 november 2009 @ 14:12:
[...]

Je moet er wel erg mee oppassen hoor. Ik zou between gewoon niet gebruiken in datetime's. Want naast dit issue kan een datetime (datetime2 wel) niet zomaar alle times aan. Als je bijvoorbeeld het hele jaar wilt hebben in een between, dan moet je doen:
between 2006-01-01T00:00:00.000 and 2006-12-31T23:59:59.999
Maar 2006-12-31T23:59:59.999 past niet in een datetime, de laatste die er wel in past is 2006-12-31T23:59:59.997. Dus 2006-12-31T23:59:59.999 wordt naar 2007-01-01T00:00:00.000 afgerond.
Dus dan moet je eigenlijk doen:
between 2006-01-01T00:00:00.000 and 2006-12-31T23:59:59.997
wat natuurlijk een beetje vreemd is.
Of je gebruikt gewoon
date >= 2006-01-01T00:00:00.000 and date < 2007-01-01T00:00:00.000
En dan kan je de tijden weglaten:
date >= 2006-01-01 and date < 2007-01-01

O op zich heb je wel een punt hoor, en aangezien het alternatief net zo eenvoudig is zou ik zeker daarvoor kiezen. Echter is het vaak niet zo'n ramp als de randgevallen met de vorige dag meegenomen worden.

[Macros]

Woy schreef op dinsdag 03 november 2009 @ 14:17:
[...]

O op zich heb je wel een punt hoor, en aangezien het alternatief net zo eenvoudig is zou ik zeker daarvoor kiezen. Echter is het vaak niet zo'n ramp als de randgevallen met de vorige dag meegenomen worden.

De ervaring met mijn klanten is dat geen ramp, totdat ze er ineens achter komen en een bepaald rapport in bepaalde gevallen net de verkeerde data teruggeeft. Ze zitten er al niet op te wachten als een bedrag ergens met 1 cent afwijkt.

[Verwijderd]

Beste mensen bedankt voor jullie input. maaar
Dit is niet wat ik bedoel. Een polis loopt door tot de eindatum en start op een startdatum. Dus elke maand wordt het bedrag steed groter. omdat de waarde van de vorige maand ook meegenomen moet worden.

[Macros]

Is dat dan met of zonder rente?

[Woy]

Geef dan eens een eenvoudig voorbeeldje wat je precies aan data hebt, en wat je daar voor data uit wil krijgen?

[Verwijderd]

Range Jan 2009 t/m Mrt 2009

Startdatum Einddatum Bedrag
1-4-2008 31-3-2009 50
1-1-2009 28-2-2009 10
1-2-2009 28-2-2009 5
1-1-2009 31-12-2009 50

Jan 110 3
Feb 115 4
Mrt 100 2

[Woy]

Ik denk dat je dat het makkelijkst voor elkaar kunt krijgen door gewoon alle records die binnen die periode vallen te selecteren, en dan in je code met een loopje de bedragen bij elkaar optellen.

code:

foreach( month in periode )
{
    foreach( record in result )
    {
         if( isRecordActiveInMonth(record)  )
         {
              month.Total += record.Bedrag;
              month.NumActiveRecords++;
         }
    }
}

[ Voor 40% gewijzigd door Woy op 03-11-2009 20:43 ]