Omzet per kwartaal berekenen vanuit 1 Query (nieuwe klant en klant die stopt)

[dirkdrent]

Per kwartaal krijgt de klant een factuur echter betaald hij per maand, nu zou ik graag vanuit 1 query de omzet per kwartaal willen berekenen.
Het probleem waar ik tegen aan loop is dat sommige klanten starten binnen het kwartaal en dus niet de volledige 3 maanden hoeven te betalen (voorbeeld 1).
Daarnaast heb je ook klanten die geen klant maar willen zijn en stoppen binnen een kwartaal die dus niet het volledige kwartaal hoeven te betalen (voorbeeld 2).


Voorbeeld 1:
Klant A start in februari
Facturatie 1e kwartaal voor klant A wordt dan 2 maanden (februari, maart).
Facturatie 2e kwartaal voor klant A wordt dan de 3 volledige maanden (april, mei, juni)

Voorbeeld 2:
Klant B wil zijn abonnement stop zetten per 1 mei.
Facturatie 2e kwartaal voor klant B wordt dan 1 maand (april)

De svelden die ik nu in mijn query heb zijn de volgende (gebasseerd op de tabel klanten):
[Idklant]
[Naam]
[Startdatum]
[Stopdatum]
[Prijs pm incl] -----vereenvoudigd veld in de db wordt de prijs op basis van meerdere velden berekend gezien de klant op basis van aantal gebruikers het maandbedrag betaald------
Datum vandaag: (Now())
Aantal mnd klant: DateDiff("m",[startdatum],[datum vandaag])
Prijs kwartaal incl: [Prijs pm incl]*[aantal mnd klant]
---------------------Zou dan gebasseerd zijn dat ik de query selectie doe op het kwartaal b.v. >#3/31/2015# And <#6/30/2015#---------------------
Aantal mnd tot stop: DateDiff("m",[stopdatum],[datum vandaag])
Test: [aantal mnd klant]-[aantal mnd tot stop]
test2: [Prijs p/m incl]*[test]
test3: [Prijs kwartaal incl]-[test2]
test3: [Prijs kwartaal incl]-[test2]
Aantal klantentotaal: DCount("[Naam]","[klanten]","[naam]=True") ------ deze geeft altijd het totaal weer ondanks de filter die bij [prijs kwartaal incl] is aangebracht

Ik heb het idee dat ik in de buurt ben van de oplossing, echter mis ik volgens mij iets heel kleins. Dacht er zelf aan om 4 extra velden toe te voegen aan de query waarbij ik de datums op geef van de eind kwartalen dus [kwartaal1] 30-mrt-2015, [kwartaal2] 31-jul-2015, [kwartaal3] 31-okt-2015 en [kwartaal4] 31-12-2015 en dan extra berekende velden aan te maken die de maanden dan berekenen.
Ook waar ik aan zat te denken is bij het aantal maanden klant om hier een criteria(filter) op te zetten van <3 mnd gezien een kwartaal 3 maanden duurt en je op deze manier ook de klanten er uit vangt die maar 2 maanden in dat kwartaal het systeem hebben gebruikt. Waar ik tegen aan loop is dat ik dan de gebruikers die gestop zijn er niet uit krijg.

Heb het idee dat ik te moeilijk denk dus ik ben echt opzoek naar iemand die mij hier mee kan helpen aan een oplossing. Wellicht is dit al een keer eerder aan bod gekomen op het forum ik kon het echter niet vinden in combinatie met nieuwe klanten en klanten die opzeggen.

ps. In de bijlage een voorbeeld van de database, ik heb de Engelse versie van AccessBekijk bijlage Voorbeeld db.zip

 

[OctaFish]

Kwartalen kun je simpel filteren uit een datum(reeks) (functie DatePart) en de eerste en laatste maand van een datum van een klant binnen dat kwartaal ook. Het hoogste maandgetal minus het laagste +1 geeft je het aantal maanden dat de klant moet betalen.

 

[dirkdrent]

Ik heb nu op basis van de startdatum m.b.h.v. datepart bepaald in welk kwartaal een klant is gestart, het is mij nog niet geheel duidelijk hoe ik binnen een kwartaal het hoogste maandgetal minus het laagste +1 moet doen...

 

[OctaFish]

Je kunt vrij simpel de gegevens per kwartaal bekijken, met deze query:

SELECT Klanten.IDklant, Klanten.Naam, Year([Startdatum]) AS Jaar, DatePart("q",[Startdatum],2,2) AS Kwartaal, Klanten.Startdatum, Klanten.Stopdatum, 
Sum(([ion]/2350*137)*0.79) AS Prijs, Sum(([ion]/2350)*137) AS [Prijs p/m incl], IIf([startdatum]<DateSerial(Year(Date()),(DatePart("q",[startdatum],2,2)-1)*3+1,1),
Month(DateSerial(Year(Date()),(DatePart("q",[startdatum],2,2)-1)*3+1,1)),Month([startdatum])) AS startM, IIf([Stopdatum] Is Null,Month(DateSerial(Year(Date()),
(DatePart("q",[Startdatum],2,2)-1)*3+3,1)),Month([Stopdatum])) AS StopM
FROM Klanten
GROUP BY Klanten.IDklant, Klanten.Naam, Year([Startdatum]), DatePart("q",[Startdatum],2,2), Klanten.Startdatum, Klanten.Stopdatum, IIf([startdatum]<
DateSerial(Year(Date()),(DatePart("q",[startdatum],2,2)-1)*3+1,1),Month(DateSerial(Year(Date()),(DatePart("q",[startdatum],2,2)-1)*3+1,1)),Month([startdatum]))
HAVING (((DatePart("q",[Startdatum],2,2))=DatePart("q",Date(),2,2)-Nz([Typ het gewenste kwartaal:],0)))
ORDER BY Klanten.IDklant, Year([Startdatum]), DatePart("q",[Startdatum],2,2);

Hier zit nog niet de berekening in per maand die je zoekt, omdat ik nog niet helemaal doorheb hoe je de berekening wilt hebben. Ik vermoed dat, als de einddatum leeg is, het contract doorloopt en je dus het hele kwartaal wilt zien. Probleem is dat je maar één kwartaal tegelijk kunt zien, en de query nog niet de juiste eindkwartalen toont. Dit omdat er nu alleen een check zit of de einddatum leeg is of niet. Je zult dus nog een extra voorwaarde erbij moeten maken die het juiste kwartaalnummer pakt. Maar je moet natuurlijk ook nog zelf iets te knutselen hebben .

 

[dirkdrent]

Wederom bedankt.... Ga er morgen mee stoeien. Het klopt idd dat wanneer einddatum leeg is het contract gewoon door loopt... Houdt je op de hoogte