A horrible SQL thing.

SQLHell.sql

SELECT 
	whlnew.sku, 
    whlnew.labelshort, 
    whlnew.profit * sales.sold as Profit,
    sales.channelTitle,
	sales.Orders,
    sales.Multis,
    sales.sold,
    sales.turnover,
    CAST(CONCAT(SUBSTRING(listingdates.ListedDate,7,4),'-',SUBSTRING(listingdates.ListedDate,4,2),'-',SUBSTRING(listingdates.ListedDate,1,2)) AS DATE) as listdate,
    listingdates.Fullname,
    whlnew.retail, 
    sales.samemultis, 
    whlnew.margin, 
    Avg_SalePrice,
    Datediff(CAST('2016-11-13' AS DATE),CAST(CONCAT(SUBSTRING(listingdates.ListedDate,7,4),'-',SUBSTRING(listingdates.ListedDate,4,2),'-',SUBSTRING(listingdates.ListedDate,1,2)) AS DATE)) as Age_Days,
    ((sales.orders / Datediff(CAST('2016-11-13' AS DATE),CAST(CONCAT(SUBSTRING(listingdates.ListedDate,7,4),'-',SUBSTRING(listingdates.ListedDate,4,2),'-',SUBSTRING(listingdates.ListedDate,1,2)) AS DATE)))*7) as SalesPerWeek
    
FROM 
	whldata.whlnew
LEFT JOIN 
	(SELECT 
		sku, 
        channelTitle, 
        count(orderId) as Orders, 
        SUM(case when ismixedorder='True' then 1 else 0 end) as Multis, 
        sum(salequantity) as sold, 
        sum(saleprice + postagepaid) as turnover, 
        customlabel, 
        SUM(case when saleQuantity>1 then 1 else 0 end) as SameMultis,
        Avg(saleprice) as Avg_Saleprice
        
	FROM 
		whldata.newsales_raw 
	WHERE 
		CHAR_LENGTH(customlabel) > 17
	GROUP BY 
		channelTitle) as sales ON whlnew.sku = sales.sku 
JOIN 
	(SELECT 
		shortsku, 
		payrollno, 
		concat(employees.firstname, ' ', employees.surname) as Fullname,
		datetimechanged as ListedDate
	FROM 
		whldata.sku_changelog 
	JOIN 
		whldata.employees ON sku_changelog.payrollid=employees.payrollno 
	WHERE 
		reason LIKE '%Marked as listed%' 
	GROUP BY 
		shortsku) as listingdates  ON listingdates.shortsku = whlnew.shortsku
WHERE 
	NOT whlnew.packsize=0 AND
    whlnew.sku > '10100000000' AND 
    CAST(CONCAT(SUBSTRING(listingdates.ListedDate,7,4),'-',SUBSTRING(listingdates.ListedDate,4,2),'-',SUBSTRING(listingdates.ListedDate,1,2)) AS DATE) BETWEEN CAST('2016-10-01' AS DATE) and CAST('2016-11-13' AS DATE)
UNION ALL
SELECT 
	whlnew.sku, 
    whlnew.labelshort, 
    whlnew.profit * sales.sold as Profit,
    sales.channelTitle,
	sales.Orders,
    sales.Multis,
    sales.sold,
    sales.turnover,
    CAST(CONCAT(SUBSTRING(listingdates.ListedDate,7,4),'-',SUBSTRING(listingdates.ListedDate,4,2),'-',SUBSTRING(listingdates.ListedDate,1,2)) AS DATE) as listdate,
    listingdates.Fullname,
    whlnew.retail, 
    sales.samemultis, 
    whlnew.margin, 
    Avg_SalePrice,
    Datediff(CAST('2016-11-13' AS DATE),CAST(CONCAT(SUBSTRING(listingdates.ListedDate,7,4),'-',SUBSTRING(listingdates.ListedDate,4,2),'-',SUBSTRING(listingdates.ListedDate,1,2)) AS DATE)) as Age_Days,
    ((sales.orders / Datediff(CAST('2016-11-13' AS DATE),CAST(CONCAT(SUBSTRING(listingdates.ListedDate,7,4),'-',SUBSTRING(listingdates.ListedDate,4,2),'-',SUBSTRING(listingdates.ListedDate,1,2)) AS DATE)))*7) as SalesPerWeek
    
FROM 
	whldata.whlnew
Right JOIN 
	(SELECT 
		sku, 
        channelTitle, 
        count(orderId) as Orders, 
        SUM(case when ismixedorder='True' then 1 else 0 end) as Multis, 
        sum(salequantity) as sold, 
        sum(saleprice + postagepaid) as turnover, 
        customlabel, 
        SUM(case when saleQuantity>1 then 1 else 0 end) as SameMultis,
        Avg(saleprice) as Avg_Saleprice
        
	FROM 
		whldata.newsales_raw 
	WHERE 
		CHAR_LENGTH(customlabel) > 17
	GROUP BY 
		channelTitle) as sales ON whlnew.sku = sales.sku 
JOIN 
	(SELECT 
		shortsku, 
		payrollno, 
		concat(employees.firstname, ' ', employees.surname) as Fullname,
		datetimechanged as ListedDate
	FROM 
		whldata.sku_changelog 
	JOIN 
		whldata.employees ON sku_changelog.payrollid=employees.payrollno 
	WHERE 
		reason LIKE '%Marked as listed%' 
	GROUP BY 
		shortsku) as listingdates  ON listingdates.shortsku = whlnew.shortsku
WHERE 
	NOT whlnew.packsize=0 AND
    whlnew.sku > '10100000000' AND 
    CAST(CONCAT(SUBSTRING(listingdates.ListedDate,7,4),'-',SUBSTRING(listingdates.ListedDate,4,2),'-',SUBSTRING(listingdates.ListedDate,1,2)) AS DATE) BETWEEN CAST('2016-10-01' AS DATE) and CAST('2016-11-13' AS DATE)