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:
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