SET @from_date= CURRENT_DATE;
SET @to_date= CURRENT_DATE;

SELECT 
	good_receipt_details.good_receipt_date,
	items.item_name,
	item_sub_category.sub_category_name,
	items.indv_item_qty,
	items.pack_size,
	get_item_in_out_stock(items.id, 'X', 'S', 'I', good_receipt_details.good_receipt_date, good_receipt_details.good_receipt_date) as whole_in,
	get_item_in_out_stock(items.id, 'X', 'LS', 'I', good_receipt_details.good_receipt_date, good_receipt_details.good_receipt_date) as loose_in,
	get_item_in_out_stock(items.id, 'X', 'S', 'O', good_receipt_details.good_receipt_date, good_receipt_details.good_receipt_date) as whole_out,
	get_item_in_out_stock(items.id, 'X', 'LS', 'O', good_receipt_details.good_receipt_date, good_receipt_details.good_receipt_date) as loose_out

FROM 
	good_receipt_details

INNER JOIN 
	items
	ON items.id= good_receipt_details.item_id AND items.is_active='Y'

LEFT JOIN 
	item_sub_category
	ON item_sub_category.id=items.item_sub_category

WHERE 
	good_receipt_details.good_receipt_date>=@from_date
	AND good_receipt_details.good_receipt_date<=@to_date

GROUP BY
	good_receipt_details.good_receipt_date,
	items.item_name,
	item_sub_category.sub_category_name,
	items.indv_item_qty,
	items.pack_size,
	items.id
