I have good code, but need to repair them
Item code, name, Third column is procurement, fourth column is sale, and last column i difference (5) (column 3-column 4)
In my code result is just item if have in procurement
CASE 1
Exmp Coca cola
Procurement 5
Sale 3
Result 2
That is ok, problem is next.
CASE 2
If I not procure Coca Cola need to be next
Procurement 0
Sale 2
Result -2
In my code result is empty in CASE 2
Thanks for help
What I have tried:
SELECT x1.sifra AS 'Šifra',
x1.naziv AS 'Naziv',
CONVERT(DECIMAL(16, 2), Sum(Isnull(x1.nabavka, 0))) AS 'Nab. (kol.)',
CONVERT(DECIMAL(16, 2), Sum(Isnull(x2.prodaja, 0))) AS 'Prod. (kol.)',
CONVERT(DECIMAL(16, 2), Sum(CONVERT(DECIMAL(16, 2), x1.nabavka) - (
Isnull(x2.prodaja, 0) ))) AS 'Stanje (kol.)'
FROM (SELECT naziv,
sifra,
Sum(Isnull(kolicina, 0)) AS Nabavka
FROM (SELECT naziv AS naziv,
sifra,
Sum(Isnull(kolicina, 0)) AS kolicina
FROM popis_roba
GROUP BY naziv,
sifra) a
GROUP BY naziv,
sifra) x1
LEFT JOIN(SELECT roba,
sifra,
Sum(Isnull(kolicina, 0)) AS Prodaja
FROM (SELECT roba,
sifra,
Sum(Isnull(kolicina, 0)) AS kolicina
FROM mp_racun_roba
GROUP BY roba,
sifra) b
GROUP BY roba,
sifra) x2
ON x2.roba = x1.naziv
WHERE x1.naziv IS NOT NULL
GROUP BY x1.naziv,
x1.sifra