Goran Bibic

Goran Bibic

  • 451
  • 2.9k
  • 181.2k

Select values frow both query MS SQL

Mar 22 2020 4:18 AM
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
 
 
  1. SELECT x1.sifra                                              AS 'Šifra',  
  2.        x1.naziv                                              AS 'Naziv',  
  3.        CONVERT(DECIMAL(16, 2), Sum(Isnull(x1.nabavka, 0)))   AS 'Nab. (kol.)',  
  4.        CONVERT(DECIMAL(16, 2), Sum(Isnull(x2.prodaja, 0)))   AS 'Prod. (kol.)',  
  5.        CONVERT(DECIMAL(16, 2), Sum(CONVERT(DECIMAL(16, 2), x1.nabavka) - (  
  6.                                    Isnull(x2.prodaja, 0) ))) AS 'Stanje (kol.)'  
  7. FROM  (SELECT naziv,  
  8.               sifra,  
  9.               Sum(Isnull(kolicina, 0)) AS Nabavka  
  10.        FROM  (SELECT naziv                    AS naziv,  
  11.                      sifra,  
  12.                      Sum(Isnull(kolicina, 0)) AS kolicina  
  13.               FROM   popis_roba  
  14.               GROUP  BY naziv,  
  15.                         sifra) a  
  16.        GROUP  BY naziv,  
  17.                  sifra) x1  
  18.       LEFT JOIN(SELECT roba,  
  19.                        sifra,  
  20.                        Sum(Isnull(kolicina, 0)) AS Prodaja  
  21.                 FROM  (SELECT roba,  
  22.                               sifra,  
  23.                               Sum(Isnull(kolicina, 0)) AS kolicina  
  24.                        FROM   mp_racun_roba  
  25.                        GROUP  BY roba,  
  26.                                  sifra) b  
  27.                 GROUP  BY roba,  
  28.                           sifra) x2  
  29.              ON x2.roba = x1.naziv  
  30. WHERE  x1.naziv IS NOT NULL  
  31. GROUP  BY x1.naziv,  
  32.           x1.sifra 
 

Answers (9)