I CAN'T CONECT TWO COLUMNS ITS SIMPLE AN JUST SIMPLE SOLUTION
WHERE I WRONG?
Query result need instead null to be values
select x1.sifra, x2.naziv, (CONVERT(decimal(16, 2), ISNULL(x1.Nabavka, 0))) as 'Nab. (kol.)', SUM(CONVERT(decimal(16, 2), ISNULL(x2.Prodaja, 0))) as 'Prod. (kol.)',
CONVERT(decimal(16, 2), ISNULL(x1.Nabavka, cast(0 as decimal(3,2))) - ISNULL(x2.Prodaja, cast(0 as decimal(3,2)))) as 'Stanje (kol.)'
from(select naziv, sifra, sum(kolicina) as Nabavka
from(
select roba as naziv, sifra, convert(float, kolicina) as kolicina from vp_kalkulacija_roba where id_fakture IN(select id from dbo.vp_kalkulacija_lista where magacin = (select vp_magacin from dbo.podaci_o_korisniku))
) a group by naziv, sifra) x1
FULL join(select naziv, sifra, sum(kolicina) as Prodaja
from(
select roba as naziv, sifra, convert(float, kolicina) as kolicina from vp_faktura_roba where id_fakture IN(select id from vp_faktura_lista where magacin = (select vp_magacin from dbo.podaci_o_korisniku))
) b
group by naziv, sifra) x2
on x1.naziv = x2.naziv
group by x1.sifra, x2.naziv, x1.Nabavka, x2.Prodaja
Query result
sifra naziv Nab. (kol.) Prod. (kol.) Stanje (kol.)
NULL NULL 0.00 1.00 -1.00
NULL NULL 0.00 50.00 -50.00
72096099 CADRE UNITE DE COUPE 20.00 0.00 20.00
800-40962 SPANNBOLZEN 1 ZSU-400 7.00 0.00 7.00
800-40964 SPANNBOLZEN 3 ZSU-400 3.00 0.00 3.00
800-40971 PRISMENBACKE ZSU-400 14.00 0.00 14.00
Goran BibicPosted Oct 11, 2022, 9:38 AM
I need all values from multiple tables code (sifra), name (naziv), Nab. (kol.) (Add qty), Prod. (kol.), (Sold qty), Stanje (kol.), (Stock qty)
qty are ok counting, code (sifra), name (naziv) not write names and codes
Goran BibicPosted Oct 11, 2022, 9:35 AM
Problem is when put sum operator in quantity.
Values are ok, and quantity this is problem
Result
If change
Other values are null, in both variant quantity is ok
Nitin SontakkePosted Oct 11, 2022, 3:38 AM
Is this SQL Server? If yes, if possible, you are better of writing a stored procedure.
It is always a good practice to do work in small chunks instead of lumping it all together in a big select statement.
For example, the query select vp_magacin from dbo.podaci_o_korisniku appears twice.
First, is it guaranteed to return a SINGLE value? If yes, it is a good idea to store that value in a variable and use that variable instead.
Hope you get the point.