Click here to Skip to main content
14,486,704 members
Rate this:
Please Sign up or sign in to 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
Updated 24-Mar-20 0:49am
v2
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

Rate this:
Please Sign up or sign in to vote.

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
   
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 5 days ago
   
If don't help do not comment. Thank you
Jörgen Andersson 5 days ago
   
I'm sorry my solution didn't help you. I will not help you again.
Goran Bibic 4 days ago
   
Not for you...this is for Maciej Los ...thank you for solution

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100