Click here to Skip to main content
15,847,750 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Result:
row_num	Naziv	J.M.	Količina	Izlaz
1	AJVAR	Kilogram	17.51	
2	BIBER	Kilogram	2.50	
3	BIJELI LUK GRANULE	Kilogram	17.25	

.
.
.
etc.
on end:
55	VEGETA	Kilogram	0.00	
56	VRHNJE ZA KUHANJE	Litar	0.00	
1			0.00	SIS CEVAP
2			0.00	SREDNJI CEVAP
3			0.00	VELIKI CEVAP


What I have tried:

SQL
WITH cteCombined AS (
  select  ROW_NUMBER() OVER (ORDER BY naziv) row_num,
  naziv as [Naziv], jedinica_mjere [J.M.], sum(kolicina) as [Kolicina], _
        '' as [Izlaz] from dbo.popis_repromaterijal_roba
  where (select redni_broj from dbo.popis_repromaterijal_lista _
  where redni_broj IS NOT NULL AND id = id_fakture) IS NOT NULL
  group by sifra,  naziv, jedinica_mjere
),

cteCombined2 AS (
    select ROW_NUMBER() OVER (ORDER BY roba) row_num,
 '' as [Naziv], '' as [J.M.], '0' as [Kolicina], _
    roba as [Izlaz] from dbo.mp_racun_roba
    where id_fakture IN (select id from mp_racun_lista _
    where datum = '2023-10-01' )
AND roba=(select naziv from dbo.roba_usluge _
    where podgrupa_artikala='HRANA' and roba_usluge.naziv=mp_racun_roba.roba)
group by sifra, roba
),
cte AS (
    SELECT
        row_num,
        [Naziv],
        [J.M.],
        SUM([Kolicina]) AS [Kolicina],
        [Izlaz]
    FROM cteCombined
	where [Kolicina] > 0
    GROUP BY
        row_num,
        [Naziv],
        [J.M.],
        [Izlaz]
),
cte2 AS (
    SELECT
        row_num,
        [Naziv],
        [J.M.],
        [Kolicina],
        [Izlaz]
    FROM cteCombined2
    GROUP BY
        row_num,
        [Naziv],
        [J.M.],
        [Izlaz],
        [Kolicina]
)
SELECT
    row_num,
    [Naziv],
    [J.M.],
   [Kolicina],
    [Izlaz]
FROM cte
GROUP BY
    row_num,
    [Naziv],
    [J.M.],
    [Kolicina],
    [Izlaz]
UNION ALL
SELECT
    row_num,
    [Naziv],
    [J.M.],
    [Kolicina],
    [Izlaz]
FROM cte2
GROUP BY
    row_num,
    [Naziv],
    [J.M.],
    [Kolicina],
    [Izlaz]
ORDER BY row_num
Posted
Updated 24-Oct-23 2:22am
v3
Comments
OriginalGriff 22-Oct-23 11:37am    
And?
What does it do that you didn't expect, or not do that you did?
What have you tried to do to find out why?
Are there any error messages, and if so, where and when? What did you do to make them happen?

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your database, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900