Select values frow both query MS SQL

Question

1.00/5 (1 vote)

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 21-Mar-20 23:14pm

Goran Bibic

Updated 24-Mar-20 0:49am

v2

Add a Solution

Comments

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 ?

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2020-03-24T00:50:00

Solution 1

I guess you are after something like this:

SQL

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

Posted 24-Mar-20 0:50am

Jörgen Andersson

Comments

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

Jörgen Andersson 24-Mar-20 14:42pm

For the record, I do not consider myself at the level of Richard. :)

Maciej Los 24-Mar-20 15:53pm

;)

Goran Bibic 25-Mar-20 2:27am

If don't help do not comment. Thank you

Jörgen Andersson 25-Mar-20 3:42am

I'm sorry my solution didn't help you. I will not help you again.

Goran Bibic 26-Mar-20 2:12am

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

CHill60 9-Apr-20 6:44am

It is not for you to ask members not to comment. The comment was directed at Jörgen not you. Sometimes conversations like this lead to better answers, they definitely lead to a better sense of community!

Select values frow both query MS SQL

1 solution

Solution 1

Add your solution here

Preview 0

Select values frow both query MS SQL

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us