SELECT
	so.ShopOrder_ID,
	LPAD(so.ShopOrder_OrderNumber, 6, '0') AS Bestellnummer,
	CONCAT(sc.ShopCustomer_Person_Lastname,  ' ', sc.ShopCustomer_Person_Firstname) Name,
	CAST(so.Table_Create AS DATE) AS Bestelldatum,
	so.ShopOrder_DeliveryDate AS Lieferdatum,
	DATEDIFF(so.ShopOrder_DeliveryDate, CAST(so.Table_Create AS DATE)) AS `Lieferzeit (Tage)`
FROM ShopOrder AS so
INNER JOIN ShopCustomer AS sc ON (sc.ShopOrder_ID = so.ShopOrder_ID)
WHERE
	so.ShopOrderState_Code = 'delivered'
	AND so.ShopOrderPaymentState_Code IN('payed', 'open')
	AND so.ShopOrder_ID NOT IN (
		SELECT soj.ShopOrder_ID FROM ShopOrderJournal AS soj WHERE soj.ShopOrderJournalType_Code = 'invoice_sent'
	)
ORDER BY so.Table_Create ASC