SQL Examples

This chapter provides a number of SQL examples. Substitution parameter examples and a list of substitution parameters are included:

This query returns the materials moved by units of equipment between source and destination locations during one or more shifts. The sum of tonnes moved for each equipment unit during each shift is also reported:

SELECT Shkey AS Shift, --Shift during which materials moved
  Equipment_Description AS Truck, --Description of Equipment
  Source_Description, --Description of location tonnes moved from
  Destination_Description, --Description of location tonnes moved to
  Material_Description, --Description of material moved
  SUM(measurevalue) AS VALUE --Sum of tonnes moved
FROM alloctntimestamp a  
JOIN measuretimestamp m  
     ON a.tskey = m.tskey  
JOIN V_EQUIPMENT e  
     ON e.EQUIPMENTCODE = a.EQUIPMENT  
JOIN V_EQUIPMENT_MODEL em  
     ON e.Equipment_Model_Code = em.Equipment_Model_Code  
JOIN V_EQUIPMENT_FUNCTION ef  
     ON em.Equipment_Model_Function_Code = ef.Equipment_Function_Code  
JOIN V_LOCATIONS  
     ON a.Location = V_LOCATIONS.SourceCode  
JOIN V_DESTINATIONS d  
     ON a.destn = d.destination_Code  
JOIN V_MEASCODE  
     ON V_MEASCODE.MeasureCode = m.MeasCode  
JOIN V_SHIFTLOG  
     ON a.shkey = shiftcode  
JOIN V_MATERIAL ma  
     ON a.MATERIAL = ma.MaterialCode  
JOIN V_CATEGORY mc  
     ON ma.Material_Category_Code = mc.Material_Category_Code  
JOIN V_GROUP mg  
     ON mc.Material_Category_Group_Code = mg.Material_Group_Code  
WHERE Equipment_Function_Code = 'HAULING' --This filter limits the selection to Hauling equipment
  AND Period_Month = 'Oct-2013' -- and tonnes moved during October 2013
  AND meascode = 'TONNE'  
GROUP BY Shkey,  
  Equipment_Description,  
  Source_Description,  
  Destination_Description,  
  Material_Description,  

This query returns the Drill measures recorded at various locations by equipment during a given period. The sum of the measures recorded during the period for each equipment unit is also reported:

SELECT Shkey AS Shift, --Shift during which measures recorded
  Equipment_Description AS Equipment, --Description of Equipment
  Source_Description, --Description of location measure recorded at
  MeasCode, --Code of measure recorded
  Measure_Description, --Description of measure recorded
  SUM(measurevalue) AS VALUE --Sum of measure values
FROM alloctntimestamp a  
JOIN measuretimestamp m  
     ON a.tskey = m.tskey  
JOIN V_EQUIPMENT e  
     ON e.EQUIPMENTCODE = a.EQUIPMENT  
JOIN V_EQUIPMENT_MODEL em  
     ON e.Equipment_Model_Code = em.Equipment_Model_Code  
JOIN V_EQUIPMENT_FUNCTION ef  
     ON em.Equipment_Model_Function_Code = ef.Equipment_Function_Code  
JOIN V_LOCATIONS  
     ON a.Location = V_LOCATIONS.SourceCode  
JOIN V_MEASCODE  
     ON V_MEASCODE.MeasureCode = m.MeasCode  
JOIN V_SHIFTLOG  
     ON a.shkey = shiftcode  
WHERE Equipment_Function_Code = 'DD' --This filter limits the selection to DD equipment
  AND Period_Month = 'Oct-2013' --and to measures captured during October 2013
GROUP BY Shkey,  
  Equipment_Description,  
  Source_Description,  
  MeasCode,  
  Measure_Description  

This query reports the operating hours and the sum of the measures recorded by equipment operators during a particular shift.

SELECT Shkey AS Shift, --Shift during which measures recorded
  Operator_description AS OPERATOR, --Description of Operator
  MeasCode, --Code of measure recorded
  Measure_Description, --Description of measure recorded
  SUM(measurevalue)/3600 AS [Hours] --Sum of measure value (seconds/3600)
FROM alloctntimestamp a  
JOIN measuretimestamp m  
     ON a.tskey = m.tskey  
JOIN V_OPERATORS o  
    ON a.Operator = o.operatorcode  
JOIN V_MEASCODE  
    ON V_MEASCODE.MeasureCode = m.MeasCode  
JOIN V_SHIFTLOG  
     ON a.shkey = shiftcode  
WHERE Period_Shift_Code = '20131031P2' --This filter returns time logged for the Night Shift
  AND MeasCode = 'SECONDS' -- on the 31/10/2013
GROUP BY Shkey,  
  Operator_Description,  
  MeasCode,  
  Measure_Description  

This query has been extended to report the status as well as the operating hours and the sum of the measures recorded by a particular operator during the shift.

SELECT Shkey AS Shift, --Shift during which measures recorded
  Operator_description AS OPERATOR, --Description of Operator
  Status_Group_Description, --Description of Status Group
  Status_Description, --Description of Status
  MeasCode, --Code of measure recorded
  Measure_Description, --Description of measure recorded
  SUM(measurevalue)/3600 AS [Hours] --Sum of measure value (seconds/3600)
FROM alloctntimestamp a  
JOIN measuretimestamp m  
     ON a.tskey = m.tskey  
JOIN V_OPERATORS o  
    ON a.Operator = o.operatorcode  
JOIN V_MEASCODE  
    ON V_MEASCODE.MeasureCode = m.MeasCode  
JOIN V_SHIFTLOG  
     ON a.shkey = shiftcode  
JOIN V_STATUS s  
    ON a.STATUS = s.STATUSCODE  
JOIN V_STATUS_CATEGORY sc  
    ON s.Status_Category_Code = sc.Status_Category_Code  
JOIN V_STATUS_TYPE st  
    ON sc.Status_Category_Type_Code = st.Status_Type_Code  
JOIN V_STATUS_GROUP sg  
    ON st.Status_Type_Group_Code = sg.Status_Group_Code  
WHERE Period_Shift_Code = '20131031P2' --This filter returns time logged for the Night Shift
  AND MeasCode = 'SECONDS' --on the 31/10/2013 for a specific operator
  AND Operator_Description = 'Michael Hartzer'  
GROUP BY Shkey,  
  Operator_Description,  
  Status_Group_Description,  
  Status_Description,  
  MeasCode,  
  Measure_Description  

In the following example we want PERIOD and OPERATOR selections to be made at the time of running the report. Substitutions are shown in bold:

SELECT Shkey AS Shift, --Shift during which measures recorded
  Operator_description AS OPERATOR, --Description of Operator
  MeasCode, --Code of measure recorded
  Measure_Description, --Description of measure recorded
  SUM(measurevalue)/3600 AS [Hours] --Sum of measure value (seconds/3600)
FROM alloctntimestamp a  
JOIN measuretimestamp m  
     ON a.tskey = m.tskey  
JOIN V_OPERATORS o  
    ON a.Operator = o.operatorcode  
  AND[WhereCriteria:Operator] -- Operator now selected by substitution
JOIN V_MEASCODE  
    ON V_MEASCODE.MeasureCode = m.MeasCode  
JOIN V_SHIFTLOG  
     ON a.shkey = shiftcode  
  AND[WhereCriteria:Operator] -- Period now selected by substitution
WHERE MeasCode = 'SECONDS'  
GROUP BY Shkey,  
  Operator_Description,  
  MeasCode,  
  Measure_Description  

Another use for substitution parameters is to summarise the data in a report. You can summarise by any attribute, as long as the relevant tables and views are included in the query. In the following query [SelectCriteria"Period] and [GroupCriteria;Period] substitutions have been added and are shown in bold:

SELECT [SelectCritera:Period], --Shift now selected by substitution
  Operator_description AS OPERATOR, --Description of Operator
  MeasCode, --Code of measure recorded
  Measure_Description, --Description of measure recorded
  SUM(measurevalue)/3600 AS [Hours] --Sum of measure value (seconds/3600)
FROM alloctntimestamp a  
JOIN measuretimestamp m  
     ON a.tskey = m.tskey  
JOIN V_OPERATORS o  
    ON a.Operator = o.operatorcode  
  AND [WhereCriteria:Operator] -- Operator is selected by substitution
JOIN V_MEASCODE  
    ON V_MEASCODE.MeasureCode = m.MeasCode  
JOIN V_SHIFTLOG  
     ON a.shkey = shiftcode AND [WhereCriteria:Operator] -- Period is selected by substitution
WHERE MeasCode = 'SECONDS'  
GROUP BY [GroupCritera:Period], -- Group By Period now selected by substitution
  Operator_Description,  
  MeasCode,  
  Measure_Description