14,486,704 members
Rate this:
See more:
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 ```
Posted
Updated 24-Mar-20 0:49am
v2
ZurdoDev 23-Mar-20 7:49am

What? I have no idea what you are asking.
RamiroX 23-Mar-20 8:29am

Do you look for the UNION statement ?

Rate this:

## Solution 1

I guess you are after something like this:
```SELECT  sifra                       AS 'Šifra',
naziv                       AS 'Naziv',
Sum(nabavka)                AS 'Nab. (kol.)',
Sum(prodaja)                AS 'Prod. (kol.)',
Sum(nabavka) - SUM(prodaja) AS 'Stanje (kol.)'
FROM    (
SELECT  naziv
,sifra
,CONVERT(DECIMAL(16, 2),Isnull(kolicina, 0)) AS Nabavka
,CONVERT(DECIMAL(16, 2),0)                   AS Prodaja
FROM    popis_roba
UNION
SELECT  roba                                        AS naziv
,sifra
,CONVERT(DECIMAL(16, 2),0)                   AS Nabavka
,CONVERT(DECIMAL(16, 2),Isnull(kolicina, 0)) AS Prodaja
FROM    mp_racun_roba
)
GROUP BY naziv,sifra```
Maciej Los 24-Mar-20 10:11am

5ed!
Jörgen, you're another magician on CP forum
The first one - for me - is Richard Deeming, see: How to exit a regular expression...[^]).

Cheers!
Maciej

For the record, I do not consider myself at the level of Richard. :)
Maciej Los 24-Mar-20 15:53pm

;)
Goran Bibic 5 days ago

If don't help do not comment. Thank you

Goran Bibic 4 days ago

Not for you...this is for Maciej Los ...thank you for solution