Select values frow both query MS SQL

Goran Bibic

6y829SQL

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

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

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta6y ago
Accepted answer
If you are builing an application for a shop keeper to maintain its transaction, there should be a common table like inventory where all the items should be maintained and based on the items present in inventory table, all the transaction like purchase/sale should be made.

This will be a standard accounting architecture and it will be accurate to get the data. I had made some query based on your current requirement and uses full outer join for this to get the data form both the table, you can try and make changes accrordingly
SELECT  p.*
    , S.item
    , S.code
    ,   S.[SaleQty]

    , Difference = (ISNULL(p.pQty,0) - ISNULL(s.SaleQty,0))
FROM (
        SELECT      [Item]
                ,   [code]
                ,   [pQty]      = SUM(Qty)

        FROM    procurement
        GROUP BY    [Item]
                ,   [code]
    ) AS P
full outer JOIN
    (
        SELECT      [Item]
                ,   [code]  = code
                ,   [SaleQty]     = SUM(Qty)
        FROM    sale
        GROUP BY    [Item]
                ,   [code]
    ) AS S
ON  P.[Item]        = S.[Item]
+2
Goran Bibic6y ago
Create two tables
First id, item, code, price tbl name procurement
Second id, item, code, price tbl name sale

Clasic shop app
Undestand now?
0
Amit Gupta6y ago
Could you please post you question in english language as it is too hard to understand the column names like you had posted like itemcode, procurement etc.

If possible, keep the required 3-4 columns and then share the structure

Moreover, the link I shared is taking the data from two tables, just you need to take care of the JOINS
+1
Goran Bibic6y ago
Table from x1

CREATE TABLE [dbo].[popis_roba](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [redni_broj] [int] NULL,
    [oznaka] [varchar](255) NULL,
    [bar_kod] [varchar](255) NULL,
    [sifra] [varchar](255) NULL,
    [naziv] [varchar](255) NULL,
    [info] [varchar](255) NULL,
    [proizvodjac] [varchar](255) NULL,
    [nabavna_cijena] [decimal](18, 4) NULL,
    [kolicina] [decimal](18, 4) NULL,
    [mpc] [decimal](18, 4) NULL,
    [porez] [decimal](18, 4) NULL,
    [iznos_bez_pdv] [decimal](18, 4) NULL,
    [iznos_pdv] [decimal](18, 4) NULL,
    [iznos_sa_pdv] [decimal](18, 4) NULL,
    [id_fakture] [int] NULL,
    [jedinica_mjere] [varchar](255) NULL,
CONSTRAINT [PK_popis_roba] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Table from x2

CREATE TABLE [dbo].[mp_racun_roba](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [magacin] [varchar](255) NULL,
    [bar_kod] [varchar](255) NULL,
    [oznaka] [varchar](255) NULL,
    [roba] [varchar](255) NULL,
    [opis] [varchar](255) NULL,
    [kolicina] [numeric](18, 4) NULL,
    [cijena_bez_pdv] [numeric](18, 4) NULL,
    [iznos_bez_pdv] [numeric](18, 4) NULL,
    [pdv_procenat] [numeric](18, 4) NULL,
    [pdv] [numeric](18, 4) NULL,
    [mp_cijena] [numeric](18, 4) NULL,
    [iznos_sa_pdv] [numeric](18, 4) NULL,
    [redni_broj] [int] NULL,
    [sifra] [varchar](255) NULL,
    [id_fakture] [int] NULL,
    [tip_robe] [varchar](255) NULL,
    [jedinica_mjere] [varchar](255) NULL,
    [stara_cijena] [decimal](18, 4) NULL,
CONSTRAINT [PK_mp_racun_roba] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-1
Amit Gupta6y ago
Share the table structure so we can give you the exact query
0
Goran Bibic6y ago
I try, not solution

https://stackoverflow.com/a/5254988
-1
Amit Gupta6y ago
Try this link to fetch data https://stackoverflow.com/a/5254988
0
Goran Bibic6y ago
Amit Gupta

Question is why don't have values from x2?

Where I wrong?
-1
Amit Gupta6y ago
First of all, why converting every column to decimal (isn't the datatype is decimal by default)? this makes your query much longer
0