[MSSQL] Query voor om tabellen te combineren

[DyArt]

Ik heb een probleem waar ik niet uit kom. Ik heb drie tabellen, 1 met historie orders (HISVVI) waarin alles is vermeld incl aantallen en 1 met lopende orders (VKHDR) en een derde tabel die van de lopende orders de bestelregels bijhoudt (VKRGL) . Ik wil graag zien hoeveel er op 1 ordernummer is geleverd en nog te leveren. Het idee is om te kunnen zien hoe de orderinflow is in een bepaalde periode.De tabellen bevatten oa de volgende velden:
Tabel Historie (HISVVI)

ordernummer	hoeveelheid	Order aangemaakt
2012001	500	20120101
2012001	750	20120101
2012002	450	20120105

Tabel Lopende orders (VKHDR)

ordernummer	orderdatum
2012001	20120101
2012010	20120106
2012015	20120109

Tabel Lopende orders regels (VKRGL)

ordernummer	regelnummer	Aantal besteld
2012001	1	1000
2012001	2	500
2012010	1	800
2012015	1	500

Zoals te zien in de historie tabel kan er op 1 ordernummer meerdere keren uitgeleverd zijn, maar bij de lopende orders is het ordernummer uniek.

Nu wil ik graag als resultaat van de query per ordernummer zien hoeveel er totaal besteld is, dus alle historie + eventueel een openstaande aantal nog op het ordernummer.

Ik heb zelf het volgende al uitgezocht als query maar deze gaat fout doordat hij bij elke historie aantal het huidige openstaande aantal weergeeft. Het zou moeten zijn voor order 2012001 (500+750+1000+500), maar mijn query maakt ervan (500+1000+500+750+1000+500) omdat hij een group by maakt van de historie en van de regels. Kan iemand mij op weg helpen om dit probleem op te lossen. Het moet dus een groepering worden van de historie en deze optellen bij de groepering van de juiste orderregels behorende bij het ordernummer van de historie. Als er geen historie order is, dan alleen weergeven wat er in lopend is. Uiteindelijk moet er tevoorschijn komen hoeveel is in een week aan orders is binnengekomen kijkend in eerste instantie naar de VKHDR.DATUMORDER en als deze niet bestaat HISVVI.DATUMAANGEMAAKTORDER

SQL:

SELECT  
    CASE  
        WHEN VKHDR.ORDERNUMMER IS NULL THEN HISVVI.VERKOOPORDNR 
        ELSE VKHDR.ORDERNUMMER 
    END AS ORDERNUMMER, 
    CASE  
        WHEN VKHDR.DATUMORDER IS NULL THEN HISVVI.DATUMAANGEMAAKTORDER 
        ELSE VKHDR.DATUMORDER 
    END AS DATUM,
SUM((ISNULL(HISVVI.AANTAL,0)) + (ISNULL(VKRGL.AANTALBESTELD,0))) AS 'AANTAL', Sum(ISNULL(HISVVI.BEDRAG,0)) + Sum(ISNULL(VKRGL.REGELBEDRAG,0)) AS 'WAARDE'
FROM HISVVI
FULL JOIN VKHDR
ON HISVVI.VERKOOPORDNR = VKHDR.ORDERNUMMER
LEFT JOIN VKRGL
ON VKRGL.ORDERNUMMER = VKHDR.ORDERNUMMER
GROUP BY VKHDR.ORDERNUMMER, VKHDR.DATUMORDER, HISVVI.VERKOOPORDNR, HISVVI.DATUMAANGEMAAKTORDER
ORDER BY VKHDR.ORDERNUMMER, HISVVI.VERKOOPORDNR

Ik heb gezocht naar oplossing en geprobeerd het op te lossen met behulp van UNION en CROSS JOIN met heb het nog niet kunnen verhelpen.

[pedorus]

Beter zoeken? http://www.sqlteam.com/ar...egates-and-derived-tables

[DyArt]

Ik heb het geprobeerd zoals pedorus aangeeft maar ik loop tegen een probleem. Ik blijf maar een foutmelding krijgen "no column name was specified for column 2 ,3 and 4 of 'd' " Ik heb toch echt de kolommen een naam gegeven, zoals in onderstaand te zien is. Waar zit dan toch het probleem?

SQL:

select 
  o.AANTAL, d.AantalOrders, d.Besteld, d.Bedrag
from
  HISVVI o
inner join
(
SELECT VKHDR.ORDERNUMMER, Count(*) AS 'AantalOrders', Sum(VKRGL.AANTALBESTELD) AS 'Besteld', Sum(VKRGL.REGELBEDRAG) AS 'Bedrag'
FROM VKHDR, VKRGL
WHERE VKRGL.ORDERNUMMER = VKHDR.ORDERNUMMER
GROUP BY VKHDR.ORDERNUMMER
) d
on o.VERKOOPORDNR = d.ORDERNUMMER
GROUP BY d.ORDERNUMMER, o.VERKOOPORDNR

als ik alleen de volgende query uitvoer dan werkt het wel

SQL:

SELECT VKHDR.ORDERNUMMER, Count(*) AS 'AantalOrders', Sum(VKRGL.AANTALBESTELD) AS 'Besteld', Sum(VKRGL.REGELBEDRAG) AS 'Bedrag'
FROM VKHDR, VKRGL
WHERE VKRGL.ORDERNUMMER = VKHDR.ORDERNUMMER
GROUP BY VKHDR.ORDERNUMMER