Probleem met query opbouw

Door Brecht S op 20-01-2015 18:20 gewijzigd op 21-01-2015 16:58

2.045 views

Ik heb een tabel genaamd accountics in mijn database:


ID    Betaald    Betaald_op  betaald_in_maand   Bedrijfsnaam   Bedrag   Recurrent   Categorie
1     ja         2015-11-10  november           Bedrijf1       100      nee         IT materiaal
2     nee                                       Bedrijf2       50       nee         Software
3     ja         2014-12-20  december           Bedrijf3       250      ja          Mobiele kosten

En een tabel genaamd lijst_accountics_categorie:


ID   naam
1    Restaurant
2    Mobiele kosten
3    Software
4    IT materiaal

De relatie tussen beide tabellen zit in de categorie (tabel accountics) en de naam (tabel lijst_accountics_categorie).
Wat ik nu wil is een overzichtlijst zoals hieronder:


Code                 jan  feb  mrt  apr  mei  jun  jul  aug  sep  okt  nov  dec

Recurrent gedeelte

Mobiele kosten                                                              250    


Eenmalig gedeelte

IT-materiaal                                                            100

De namen 'recurrent gedeelte' en 'eenmalig gedeelte' komt uit de selectie in de accountics tabel of het recurrent is of niet. Bvb: recurrent=ja => moet bij recurrent gedeelte gevoegd worden, recurrent=nee => moet bij eenmalig gedeelte gevoegd worden.
Kosten die nog niet betaald zijn (betaald = nee) moeten niet in de lijsten verschijnen.
Zijn er meerdere kosten in dezelfde maand onder dezelfde categorie worden die gewoon samengeteld voor die maand.

Momenteel heb ik al het volgende gedaan:


SELECT
  SUM(totaal_incl) as tot_be_jan
FROM 
  accountics a
JOIN
  lijst_accountics_categorie lc
ON
  a.categorie = lc.naam
WHERE a.betaald_in_maand = 'januari'

En dit doe ik per maand

Daaronder doe ik dan de volgende query:


SELECT
  a.categorie
FROM 
  accountics a
JOIN
  lijst_accountics_categorie lc
ON
  a.categorie = lc.naam
WHERE a.recurrent = 'nee'

Hieronder komt dan de html tabel te staan. Waarschijnlijk kunnen deze beiden in 1 query uitgevoerd worden. Dan moet er nog een opsplitsing gebeuren van dat recurrent en eenmalig gedeelte ook. Ook de totalen zijn niet juist opgeteld hiermee. Alles wat in de maand januari staat bvb is samen opgeslagen maar niet per categorie wat dus ook niet juist is.

Mededeling:
Dit is ook gepost op: http://www.pfz.nl/forum/topic/11087-probleem-met-query-opbouw/#entry75237

Ger van Steenderen

22-01-2015 12:54

Het alternatief:


SELECT
	series.maand_nr,
	DATE_FORMAT(a.betaald_op, '%b') maand,
	c.id cat_id,
	c.naam categorie,
	SUM(CASE a.recurring WHEN 'ja' THEN totaal_incl ELSE 0 END) recurring,
	SUM(CASE a.recurring WHEN 'nee' THEN totaal_incl ELSE 0 END) eenmalig
FROM
	(
	SELECT
		@maand_nr := @maand_nr + 1 maand_nr
	FROM
		information_schema.collations
	CROSS JOIN
		(SELECT @maand_nr := 0) u
	LIMIT 12
	) series
CROSS JOIN
	lijst_accountics_categorie c
LEFT JOIN
	accountics a
	ON series.maand_nr = MONTH(a.betaald_op)
	AND c.id = a.categorie AND a.betaald_op >= CAST('2015-01-01' AS DATE)
GROUP BY
	series.maand_nr, maand, c.cat_id, categorie

Met de series subquery worden de maandnr's 1 t/m 12 gegenereerd (als alternatief voor het op het andere forum door die PG-extremist geopperde generate_series van PostGres).

Daarna een cross-join op categorien zodat alle categorieën aan alle maandnummers gekoppeld zijn.

Dan left join accountics op maand van de datum betaald en categorie.

Brecht S

22-01-2015 13:13

Thanks Ger voor de bijdrage ;-)

Reageren

Inloggen om te reageren