0

UPDATE: Rewrote the question to be more clear (hopefully)

I am using SQL Server CE 4.0 as a database.

I have 3 tables for this query:

  • Artikels: holds the data concerning a certain Artikel(product).
  • Bestellingen: Holds the general information of an order.
  • BestelDetails: Holds the lines of an order.

The following query gives me a list of all artikels with relevant data to this report

Select a.id, a.code, a.naam, a.Voorraad
From Artikels a

This query gives me a smaller list of artikels and how many of them are still needed to fullfill the remaining (partial)orders.

Select a.id, sum (bd.Hoeveelheid)-sum(bd.Voldaan)-sum (bd.Geannuleerd)-sum(bd.BackOrder) as [Open]
From Artikels a
LEFT JOIN BestelDetails bd ON a.ID = bd.ArtikelID
LEFT JOIN Bestellingen b ON bd.BestelID = b.ID
WHERE b.Status = 2
GROUP BY a.id

Now I want to add a column to the first list with the result of [OPEN] where the ID is the same and if not, add a 0 instead.

If I run

Select a.Code, a.Naam, a.Voorraad, sum(bd.Hoeveelheid)-sum(bd.Voldaan)-sum(bd.Geannuleerd) as [Open]
From Artikels a
INNER JOIN BestelDetails bd ON a.ID = bd.ArtikelID
INNER JOIN Bestellingen b ON bd.BestelID = b.ID
WHERE b.Status = 2
GROUP BY a.Code, a.Naam, a.Voorraad

I get the smaller list with the extra info for that artikel from the first list. If I drop the WHERE clause and make LEFT JOINS, I get the data I need, but the calculation in the OPEN column is wrong.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Migaroez
  • 35
  • 6
  • By this: "I only want the lines to include those of orders that have the to be processed status (1)." Do you mean you only want processed orders? Or do you only want order that haven't been processed yet? – GullitsMullet Aug 05 '15 at 10:16
  • The sum should only include Orderlines which are part of orders with status 1 *Updated the OP to include this clarification – Migaroez Aug 05 '15 at 11:31
  • Can you do a sample dump of your data and the applicable columns. Then show what you are hoping to get as a final result. Don't know why you are doing a LEFT-JOIN as without table structures we cant see other possible correlations of the data. Finally, use spaces for sample data alignment... tabs dont work well for formatting. – DRapp Aug 05 '15 at 12:05

1 Answers1

0

Do you want to display all products but only sum those with status of 1? If so I think you need a subquery in your join. Try this:

Select a.code, a.Naam, a.Voorraad, isnull(sum(orders.Hoeveelheid)-sum(orders.Voldaan)-sum(orders.Geannuleerd),0) as Besteld

from Artikels a

left join 
    (   select bd.ArtikelID, bd.BestelID, bd.Hoeveelheid, bd.Voldaan, bd.Geannuleerd
        from BestelDetails bd
            join Bestellingen b on bd.BestelID = b.ID and b.status=1    ) orders on a.ID = orders.ArtikelID 

group by a.Code, a.Naam, a.Voorraad
GullitsMullet
  • 348
  • 2
  • 8
  • 24
  • I get The following error: << The column name is not valid. [Node name (if any) = bd.Column name=Hoeveelheid >> Are you sure that SQLCE 4.0 supports subqueries? – Migaroez Aug 05 '15 at 13:20
  • You forgot to rename the fields in the Sums to orders.columname. Do you have an idea how to display a 0 when there is no result? – Migaroez Aug 05 '15 at 13:38
  • Ah, of course!. If you wrap the sum command in isnull that should do it: isnull(sum(bd.Hoeveelheid)-sum(bd.Voldaan)-sum(bd.Geannuleerd),0) as BestelD – GullitsMullet Aug 05 '15 at 13:45
  • ISNULL doesn't work in SQLCE, COALESCE does however and suits the purpose here. Thx for all your help – Migaroez Aug 05 '15 at 14:06