set @giftcardid=(select value from settings where name='GiftCardProductId');
set @enddate = date_add(date(('{ENDDATE}')), interval 1 day);
SELECT REPLACE
	( cardnum, 'GiftCard-', '' ) AS CardNumber,
	(
	SELECT
		i.postdate 
	FROM
		invoices i
		JOIN invoice_items ii ON ii.invoice_id = i.invoice_id 
	WHERE
		ii.EXTRAINFO = cardnum 
		and i.POSTDATE<@enddate
	ORDER BY
		i.postdate ASC 
		LIMIT 1 
	) AS SoldDate,
	CONVERT ((
		SELECT
			sum( PRICE ) 
		FROM
			invoice_items ii
			JOIN invoices i ON ii.INVOICE_ID = i.INVOICE_ID 
		WHERE
			i.VOIDEDINVOICE = 0 
			and i.POSTDATE<@enddate
			AND ii.EXTRAINFO = cardnum 
			AND ii.PRODUCT_ID = @giftcardid 
			),
	DECIMAL ( 10, 2 )) AS Balance 
FROM
	(
	SELECT DISTINCT
		( EXTRAINFO ) AS cardnum 
	FROM
		invoice_items ii
		JOIN invoices i ON ii.INVOICE_ID = i.INVOICE_ID 
	WHERE
		i.VOIDEDINVOICE = 0 
		and i.POSTDATE<@enddate
		AND EXTRAINFO LIKE 'GiftCard%' 
	) AS distinctcards 
HAVING
	balance > 0