This should cover the base Datatim systems and run successfully.
The only challenge will be intricately customized systems. This is additional scoping.
Go to Data Model > Add New and paste this Base Query, and then click Run.
SELECT l.*,
YEAR(l.LoadDate) AS Year,
MONTHNAME(l.LoadDate) AS MonthName,
MONTH(l.LoadDate) AS Month,
WEEK(l.LoadDate, 1) AS WeekOfYear,
DATE_FORMAT(l.LoadDate, '%Y-%m-%d') AS Load_Date,
DATE_FORMAT(l.LoadDate, '%Y-%m') AS YearMonth,
DATE_FORMAT(l.OffloadDate, '%Y-%m-%d') AS Offload_Date,
DATE_FORMAT(l.PODDate, '%Y-%m-%d') AS POD_Date,
DATE_FORMAT(l.InvDate, '%Y-%m-%d') AS Inv_Date,
DATE_FORMAT(l.PodReceivedDate, '%Y-%m-%d') AS POD_Received_Date,
CASE
WHEN l.CrNumber = '5000' THEN 'OWN'
ELSE 'SUB'
END AS Own_vs_Sub,
CASE
WHEN MONTH(l.LoadDate) = MONTH(NOW()) AND YEAR(l.LoadDate) = YEAR(NOW()) THEN 'Yes'
ELSE 'No'
END AS CurrentMonthFlag,
ROUND(l.DrValue, 2) AS Revenue,
ROUND(l.CrValue, 2) AS CostOfSales,
ROUND(l.Profit, 2) AS Gross_Profit,
ROUND(l.Profit / l.DrValue, 2) AS GP_Margin,
CASE
WHEN l.ldStatus = 0 THEN "0-WIP"
WHEN l.ldStatus = 1 THEN "1-Linked"
WHEN l.ldStatus = 2 THEN "2-Loading"
WHEN l.ldStatus = 3 THEN "3-On Route"
WHEN l.ldStatus = 4 THEN "4-Loading"
WHEN l.ldStatus = 5 THEN "5-Offloaded"
ELSE ''
END AS LoadStatus,
CASE
WHEN l.OriginFlag = 'DI' THEN "Direct Inv/CN"
WHEN l.OriginFlag = 'GI' THEN "Uninvoiced Load"
WHEN l.OriginFlag = 'MI' THEN "Invoiced Load"
ELSE ''
END AS InvoiceStatus,
CONCAT(l.LoadDescrip, " - ", l.OffloadDescrip) AS Route,
ROUND(DATEDIFF(l.PODdate, l.OffloadDate), 2) AS POD_Duration,
ROUND(DATEDIFF(l.InvDate, l.PODdate), 2) AS Invoice_Duration,
CASE
WHEN l.InvNumber = 0 THEN "Uninvoiced"
WHEN l.InvNumber > 0 THEN "Invoiced"
ELSE ''
END AS InvoicedFlag,
l.Author AS Operator,
(l.EmptyKm + l.MapKm) AS Total_KM,
CASE
WHEN l.PODnumber = 0 AND l.LdStatus = 5 AND l.Invnumber = 0 THEN "Outstanding"
ELSE "Received"
END AS PODStatus,
DATEDIFF(NOW(), DATE(l.OffloadDate)) AS DaysOffloaded
FROM vwa_loadschedule l