SELECT
	CONCAT(MIN(DATE(so.Table_Create)), ' bis ', MAX(DATE(so.Table_Create))) AS Zeitraum
FROM ShopOrder AS so
WHERE
	so.ShopOrderPaymentState_Code = 'payed'
	AND so.ShopOrderState_Code = 'delivered'
;

-- 
-- Bestell-Verteilung
-- 
SELECT
	CONCAT(LPAD(HOUR(so.Table_Create), 2, '0'), ':00') AS Stunde,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 2, 1, 0)) AS `Montag`,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 3, 1, 0)) AS `Dienstag`,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 4, 1, 0)) AS `Mittwoch`,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 5, 1, 0)) AS `Donnerstag`,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 6, 1, 0)) AS `Freitag`,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 7, 1, 0)) AS `Samstag`,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 1, 1, 0)) AS `Sonntag`,
	COUNT(*) AS `Gesamt`
FROM ShopOrder AS so
WHERE
	so.ShopOrderPaymentState_Code = 'payed'
	AND so.ShopOrderState_Code = 'delivered'
--	AND so.Shop_Code = 'default'
GROUP BY Stunde
ORDER BY HOUR(so.Table_Create);

-- 
-- Umsatz-Verteilung
-- 
SELECT
	CONCAT(LPAD(HOUR(so.Table_Create), 2, '0'), ':00') AS Stunde,
	REPLACE(SUM(IF(DAYOFWEEK(so.Table_Create) = 2, so.ShopOrder_GrandTotalExcl, 0)), '.', ',') AS `Montag`,
	REPLACE(SUM(IF(DAYOFWEEK(so.Table_Create) = 3, so.ShopOrder_GrandTotalExcl, 0)), '.', ',') AS `Dienstag`,
	REPLACE(SUM(IF(DAYOFWEEK(so.Table_Create) = 4, so.ShopOrder_GrandTotalExcl, 0)), '.', ',') AS `Mittwoch`,
	REPLACE(SUM(IF(DAYOFWEEK(so.Table_Create) = 5, so.ShopOrder_GrandTotalExcl, 0)), '.', ',') AS `Donnerstag`,
	REPLACE(SUM(IF(DAYOFWEEK(so.Table_Create) = 6, so.ShopOrder_GrandTotalExcl, 0)), '.', ',') AS `Freitag`,
	REPLACE(SUM(IF(DAYOFWEEK(so.Table_Create) = 7, so.ShopOrder_GrandTotalExcl, 0)), '.', ',') AS `Samstag`,
	REPLACE(SUM(IF(DAYOFWEEK(so.Table_Create) = 1, so.ShopOrder_GrandTotalExcl, 0)), '.', ',') AS `Sonntag`,
	REPLACE(SUM(so.ShopOrder_GrandTotalExcl), '.', ',') AS `Gesamt`
FROM ShopOrder AS so
WHERE
	so.ShopOrderPaymentState_Code = 'payed'
	AND so.ShopOrderState_Code = 'delivered'
--	AND so.Shop_Code = 'default'
GROUP BY Stunde
ORDER BY HOUR(so.Table_Create);


SELECT
	CASE DAYOFWEEK(so.Table_Create)
		WHEN '1' THEN 'Sonntag'
		WHEN '2' THEN 'Montag'
		WHEN '3' THEN 'Dienstag'
		WHEN '4' THEN 'Mittwoch'
		WHEN '5' THEN 'Donnerstag'
		WHEN '6' THEN 'Freitag'
		WHEN '7' THEN 'Samstag'
	END AS Tag,
	COUNT(*) AS `# Bestellungen`
FROM ShopOrder AS so
WHERE
	so.ShopOrderPaymentState_Code = 'payed'
	AND so.ShopOrderState_Code = 'delivered'
GROUP BY Tag
ORDER BY DAYOFWEEK(so.Table_Create);


-- 
-- Abschnitte
--    - Nacht: 0:00 - 7:00
--    - Vormittag: 7:00 - 12:00
--    - Nachmittag: 12:00 - 17:00
--    - Vorabend: 17:00 - 20:00
--    - Abend: 20:00 - 24:00
--

SELECT
	CASE
		WHEN HOUR(so.Table_Create) BETWEEN 0 AND 6 THEN 'Nacht (0:00 - 7:00)'
		WHEN HOUR(so.Table_Create) BETWEEN 7 AND 11 THEN 'Vormittag (7:00 - 12:00)'
		WHEN HOUR(so.Table_Create) BETWEEN 12 AND 16 THEN 'Nachmittag (12:00 - 17:00)'
		WHEN HOUR(so.Table_Create) BETWEEN 17 AND 19 THEN 'Vorabend (17:00 - 20:00)'
		WHEN HOUR(so.Table_Create) BETWEEN 20 AND 23 THEN 'Abend (20:00 - 24:00)'
	END AS Tagesabschnitt,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 2, 1, 0)) AS `Montag`,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 3, 1, 0)) AS `Dienstag`,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 4, 1, 0)) AS `Mittwoch`,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 5, 1, 0)) AS `Donnerstag`,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 6, 1, 0)) AS `Freitag`,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 7, 1, 0)) AS `Samstag`,
	SUM(IF(DAYOFWEEK(so.Table_Create) = 1, 1, 0)) AS `Sonntag`,
	COUNT(*) AS `Gesamt`
FROM ShopOrder AS so
WHERE
	so.ShopOrderPaymentState_Code = 'payed'
	AND so.ShopOrderState_Code = 'delivered'
--	AND so.Shop_Code = 'default'
	AND so.Table_Create BETWEEN '2013-02-01' AND '2013-07-31'
GROUP BY Tagesabschnitt
ORDER BY HOUR(so.Table_Create);


