sql vraag

[LL123]

Hey kan iemand me helpen ??
Ik kan er niet aan uit ... ben al heel de dag aan t zoeken !

Ik probeer twee tabellen, de sum samen te tellen

Ik heb een tabel orders ; id , order_date ,
een tabel orders_bestel; id , order_id , qty , unit_price , vat_code
een tabel invoices; id , due_date ,
een tabel invoices_bestel;id , invoice_id , qty , unit_price , vat_code

nu wil ik de totalen samen tellen maar aan de hand van datum <= vandaag

als resultaat zou ik dan willen ; vb
YEAR | totalenOrders | totalenInvoices
2009 | 2550.50 | 1005.20

hier is een vb query die ik gemaakt heb
Dit is met UNION
[sql]
SELECT orders.order_date AS date, YEAR(orders.order_date) AS showyear,
SUM(orders_entrys.unit_price * orders_entrys.qty + (orders_entrys.unit_price * orders_entrys.qty/100 *orders_entrys.vat_code)) AS totalOrders
FROM orders
LEFT OUTER JOIN orders_entrys ON orders.id = orders_entrys.order_id
GROUP BY YEAR(orders.order_date)
HAVING date <= date(now())
UNION
SELECT invoices.due_date AS date, YEAR(invoices.due_date) AS showyear,
SUM(invoices_entrys.unit_price * invoices_entrys.qty + (invoices_entrys.unit_price * invoices_entrys.qty/100 *invoices_entrys.vat_code))
AS totalInvoices
FROM invoices
LEFT OUTER JOIN invoices_entrys ON invoices.id = invoices_entrys.invoice_id
GROUP BY YEAR(invoices.due_date)
HAVING date <= date(now())
[/sql]
Dit is met subquery
[sql]
SELECT orders.order_date AS date, YEAR(orders.order_date) AS showyear, tblInvoices.totalInvoices AS totalInvoices,
SUM(orders_entrys.unit_price * orders_entrys.qty + (orders_entrys.unit_price * orders_entrys.qty/100 *orders_entrys.vat_code)) AS totalOrders
FROM orders
LEFT OUTER JOIN orders_entrys ON orders.id = orders_entrys.order_id
LEFT OUTER JOIN invoices ON orders.id = invoices.order_id
LEFT OUTER JOIN
(SELECT
SUM(invoices_entrys.unit_price * invoices_entrys.qty + (invoices_entrys.unit_price * invoices_entrys.qty/100 *invoices_entrys.vat_code))
totalInvoices
FROM invoices_entrys)
AS tblInvoices
ON invoices.id = tblInvoices.invoice_id
GROUP BY YEAR(orders.order_date)
HAVING date <= date(now())
[/sql]
Kan iemand me helpen ?

 

[LL123]

Ik heb zelf nog een beetje (veel) gezocht
deze query gaat er dicht bij
[sql]
SELECT files.departure_date AS date, YEAR(files.departure_date) AS showyear,
COUNT(files.departure_date <= CURDATE()) AS countfiles,
SUM(tblO.countorders) AS countorders,
SUM(tblI.countinvoices) AS countinvoices
FROM files_links
LEFT OUTER JOIN files ON files_links.file_id = files.id
LEFT OUTER JOIN (SELECT id,order_date AS date, file_link_id, YEAR(order_date) AS showyear, COUNT(id) AS countorders
FROM orders GROUP BY id, file_link_id HAVING date <= CURDATE()) AS tblO ON files_links.id = tblO.file_link_id

LEFT OUTER JOIN (SELECT id,due_date AS date,file_link_id, YEAR(due_date) AS showyear, COUNT(id) AS countinvoices
FROM invoices GROUP BY id, file_link_id HAVING date <= CURDATE()) AS tblI ON files_links.id = tblI.file_link_id
GROUP BY YEAR(files.departure_date)
HAVING YEAR(files.departure_date) <= CURDATE()
[/sql]

maar hij zou enkel de data moeten tonen kleiner dan vandaag ... hij toont gewoon alles.
[sql]YEAR(files.departure_date) <= data(now())
date <= CURDATE()[/sql]

heb ik al eens geprobeerd ! maar wil niet lukken, hij toont gewoon alles ?

 

[LL123]

Ik heb het op een andere manier opgelost
Je mag dit dan sluiten.