SET NAMES 'utf8';

SELECT 
	i.id_item AS Item,
       	i.descripcion AS Description,
	su.nombre_sucursal AS Branch,
	DATE_FORMAT(dv.fecha,"%Y-%m-01") AS Month,
       	SUM(dvi.valor) AS Net_Sales, 
	ROUND(  
		SUM(dvi.cantidad * i.peso * (IF(dvi.valor>0,1,-1))), 4
	) AS Sales_Kg,
	SUM(
		dvi.cantidad * (IF(dvi.valor >0, 1 , -1))
	) AS Sales_UoM

FROM
	documento_ventas dv
	JOIN documento_ventas_has_item dvi 
		ON ( dv.id_documento = dvi.id_documento
			AND dv.id_sucursal = dvi.id_sucursal )
	LEFT JOIN sucursal su
		ON (dv.id_sucursal = su.id_sucursal )

	JOIN item i
		USING ( id_item )

WHERE
	dv.fecha >= "2021-04-01"
	AND dv.fecha <= "2022-11-30"
	AND i.id_item IN ('260367', '260375', '260383', '260407', '260414',
		'260430', '260438', '260446', '260462', '260470', '260478',
		'260566', '260574', '260582', '260590', '260606', '260614',
		'260789', '260793', '260797', '260801', '260809', '260817',
		'260825', '430170', '430175', '430180', '430185', '430190',
		'430195', '430210', '430215', '430235', '430250', '430255',
		'430310', '430335', '430345', '430405', '430420', '430425',
		'430430', '430440', '430455', '440160', '440165', '440175',
		'440180', '440185', '440190', '440205', '440210', '440280',
		'440285', '440300', '440305', '440410', '440425', '440430',
		'440440', '440560', '440575', '440580', '440885', '440890',
		'440900', '480424', '480430', '480433', '480436', '480457',
		'480463', '480466', '480469', '480490', '480496', '480499',
		'480503', '480533', '540784', '540793', '540796', '540866',
		'540869')
	AND dv.id_sucursal IN ('bog', 'car', 'san', 'smm', 'bar', 'buc', 'ita',
		'ric', 'mal', 'mar','med','mon','pal','val','vlc')

GROUP BY
	Item, Branch, Month