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
- 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