---
title: "Drilling Summary Dashboard Specifications"
canonical: "https://servicedesk.maxgeo.com/space/DS/1421836291/Drilling%20Summary%20Dashboard%20Specifications"
format: markdown
---
| **Chart/Table Name** | **DatasourceName** | **Views/tables ** |
| --- | --- | --- |
| Chart:
Total Meters Drilled per shift | TotalMetersDrilledSource | ```sql
SELECT
CONVERT( CHAR(30), a.Log_Date, 103) AS Log_Date,
SUM(a.Day_Total) AS Day_Total_Meters,
SUM(a.Night_Total) AS Night_Total_Meters,
SUM(a.Day_Total) + SUM(a.Night_Total) AS Total_Meters,
SUM(b.Budget_Quantity)/(DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,a.Log_Date),0)))) AS Total_Budget_Meters,
COUNT(DISTINCT Hole_ID) AS Total_Holes,
CONVERT( DATE, a.Log_Date, 103) AS dateORder
FROM (
SELECT
cc.Dataset, cc.Hole_id, cc.ContractID, MachineryID, am.Drill_Item,
CASE WHEN(Shift IN (''D'',''DS'',''Day'')) THEN SUM(Quantity) ELSE 0 END AS Day_Total,
CASE WHEN(Shift IN( ''N'',''NS'',''Night'')) THEN SUM(Quantity) ELSE 0 END AS Night_Total ,
CAST(Log_Date AS DATE) AS Log_Date
FROM tblVWDASHContractCosts cc
LEFT OUTER JOIN dbo.tblContractProjectArea pa ON pa.ContractID = cc.ContractID
LEFT OUTER JOIN dbo.tblContractActivityLogMetres am ON am.ContractID = cc.ContractID AND am.ActivityID = cc.ActivityID AND am.Dataset = cc.Dataset AND am.Hole_id = cc.Hole_id AND am.depth_FROM = cc.depth_FROM
{0} AND Item_CategORy IN (''Drilling'', ''Drilling_Time'')
GROUP BY cc.Dataset,cc.Hole_id,cc.ContractID, MachineryID, am.Drill_Item, Log_Date ,Shift ) a
LEFT JOIN
tblbudgets b ON a.ContractID = b.RepORting_Type AND
b.RepORting_Period = ''Monthly'' AND
(Month(cast(b.Budget_Date AS Date)) = month(CAST(a.Log_Date AS DATE)) AND
Year(cast(b.Budget_Date AS Date)) = Year(CAST(a.Log_Date AS DATE)))
GROUP BY a.Log_Date
ORDER BY dateORder;
``` |
| Chart:
Cost per meter | CostPerMeterSource | ```sql
SELECT
DISTINCT Log_Date,
CAST(SUM(Day_Cost_Meters) AS NUMERIC(36, 2)) Day_Cost_Meters,
CAST(SUM(Night_Cost_Meters) AS NUMERIC(36, 2)) Night_Cost_Meters,
CAST(SUM(Total_Cost_Meters) AS NUMERIC(36, 2)) Total_Cost_Meters,
CAST(SUM(Total_Cost) AS NUMERIC(36, 2)) Total_Cost,
CAST(SUM(Daily_Total_Budget_Costs) AS NUMERIC(36, 2)) Daily_Total_Budget_Costs,
CAST(SUM(Daily_Total_Budget_Meters) AS NUMERIC(36, 2)) Daily_Total_Budget_Meters,
CAST(SUM(Daily_Total_Budget_CostsPerMeter) AS NUMERIC(36, 2)) Daily_Total_Budget_CostsPerMeter,
dateorder
FROM (
SELECT
RPM.Log_Date, RPM.dateorder,
CASE when Day_Total_Meter <> 0 then Day_Total_Revenue / Day_Total_Meter else 0 end AS Day_Cost_Meters,
CASE when Night_Total_Meter <> 0 then Night_Total_Revenue / Night_Total_Meter else 0 end AS Night_Cost_Meters,
CASE when Combined_Total_Meter <> 0 then Combined_Total_Revenue / Combined_Total_Meter else 0 end AS Total_Cost_Meters,
Combined_Total_Revenue AS Total_Cost, Daily_Total_Budget_Costs, Daily_Total_Budget_Meters, Daily_Total_Budget_CostsPerMeter
FROM (
SELECT
CONVERT( CHAR(30), a.Log_Date, 103) AS Log_Date,
CONVERT( DATE, a.Log_Date, 103) dateORder,
SUM(Day_Total_Revenue) AS Day_Total_Revenue,
SUM(Night_Total_Revenue) AS Night_Total_Revenue,
SUM(Day_Total_Revenue) + SUM(Night_Total_Revenue) AS Combined_Total_Revenue,
SUM(Day_Total_Meter) AS Day_Total_Meter,
SUM(Night_Total_Meter) AS Night_Total_Meter,
SUM(Day_Total_Meter) + SUM(Night_Total_Meter) AS Combined_Total_Meter ,
SUM(b.Budget_Quantity)/(DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,a.Log_Date),0)))) AS Daily_Total_Budget_Costs,
SUM(b.Budget_Amount)/(DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,a.Log_Date),0)))) AS Daily_Total_Budget_Meters,
Case when (SUM(b.Budget_Amount)/(DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,a.Log_Date),0))))) <> 0 then
SUM(b.Budget_Quantity)/(DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,a.Log_Date),0))))/
SUM(b.Budget_Amount)/(DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,a.Log_Date),0)))) end AS Daily_Total_Budget_CostsPerMeter
FROM (
SELECT
a.Dataset,a.Hole_ID,a.ContractID,MachineryID, am.Drill_Item, CONVERT( DATE, Log_Date, 103) AS Log_Date,
CASE WHEN(Shift IN (''D'',''DS'',''Day'')) THEN SUM(TotalCost) ELSE 0 END AS Day_Total_Revenue,
CASE WHEN(Shift IN( ''N'', 'NS'',''Night'')) THEN SUM(TotalCost) ELSE 0 END AS Night_Total_Revenue,
CASE WHEN(Shift IN (''D'',''DS'',''Day'')) THEN SUM(CASE WHEN Item_CategORy IN(''Drilling'', ''Drilling_Time'') THEN Quantity ELSE 0 END) ELSE 0 END AS Day_Total_Meter,
CASE WHEN(Shift IN( ''N'',''NS'',''Night'')) THEN SUM(CASE WHEN Item_CategORy IN(''Drilling'', ''Drilling_Time'') THEN Quantity ELSE 0 END) ELSE 0 END AS Night_Total_Meter
FROM tblVWDASHContractCosts a
LEFT OUTER JOIN dbo.tblContractProjectArea pa ON pa.ContractID = a.ContractID
LEFT OUTER JOIN dbo.tblContractActivityLogMetres am ON am.ContractID = a.ContractID AND am.ActivityID = a.ActivityID
AND am.Dataset = a.Dataset AND am.Hole_id = a.Hole_id AND am.depth_FROM = a.depth_FROM
{0}
GROUP BY a.Dataset,a.Hole_ID,a.ContractID, MachineryID, am.Drill_Item, Log_Date, Shift
) a
LEFT JOIN tblbudgets b ON
a.ContractID = b.RepORting_Type AND
b.RepORting_Period = ''Monthly'' AND
(Month(cast(b.Budget_Date AS DATE)) = Month(CAST(a.Log_Date AS DATE)) AND
Year(cast(b.Budget_Date AS DATE)) = Year(CAST(a.Log_Date AS DATE)))
GROUP BY a.Log_Date
) RPM
)a1
GROUP BY Log_Date, dateorder
ORDER BY dateorder;
``` |
| Chart:
Drill Hole Progress | DrillHoleProgressSource | ```sql
SELECT
tblDHColl.DataSet, tblDHColl.Hole_ID,
DD.DP_Depth AS Current_Depth,
DD.DP_TotalDepth AS Total_Depth,
tblDHColl.Max_Depth AS Planned_Depth,
DD.Contract_End_Date AS Contract_End_Date,
DD.Activity_Log_Start_Date AS Drill_Activity_Start_Date,
DD.Activity_Log_End_Date AS Drill_Activity_End_Date,
tblDHColl.Hole_Purpose, tblDHColl.Hole_Status,
tblDHColl.Prospect, DD.MachineryID AS MachineryID,
tblDHColl.Date_Completed AS Date_Completed,
DD.ContractID, dd.Project_Area
FROM (
SELECT
b.ContractID, d.Project_Area,
a.DataSet, a.Hole_ID,
Max(a.Depth_To) AS DP_Depth,
sum(a.Depth_To - a.Depth_FROM) AS DP_TotalDepth,
MIN(b.Activity_Log_Date) AS Activity_Log_Start_Date,
Max(c.Contract_End_Date) AS Contract_End_Date,
MAX(b.Activity_Log_Date) AS Activity_Log_End_Date,
Min(b.MachineryID) AS MachineryID
FROM dbo.tblContractActivityLogMetres a
INNER JOIN dbo.tblContractActivityLog b ON a.ActivityID = b.ActivityID
INNER JOIN dbo.tblContractHeader c ON b.ContractID = c.ContractID
LEFT OUTER JOIN dbo.tblContractProjectArea d ON d.ContractID = c.ContractID
LEFT OUTER JOIN dbo.tblContractActivityLogMetres am ON am.ContractID = a.ContractID AND am.ActivityID = a.ActivityID
AND am.Dataset = a.Dataset AND am.Hole_id = a.Hole_id AND am.depth_FROM = a.depth_FROM
{0}
GROUP BY a.DataSet, a.Hole_ID, d.Project_Area, b.ContractID
) AS DD
INNER JOIN dbo.tblDHColl ON
DD.DataSet = dbo.tblDHColl.DataSet AND
DD.Hole_ID = dbo.tblDHColl.Hole_ID;
``` |
| Date Filter Range SELECTOR:
Total Cost Range SELECTOR | costPerMeterSource.Log_Date | |
| Chart:
Item CategORies | ItemCategORiesSource | ```sql
SELECT
Item_Category,
SUM(TotalCost) Totalcost,
SUM(TotalCost) Quantity
FROM tblVWDASHContractCosts a
LEFT OUTER JOIN dbo.tblContractProjectArea b ON b.ContractID = a.ContractID
LEFT OUTER JOIN dbo.tblContractActivityLogMetres am ON am.ActivityID = a.ActivityID AND am.ContractID = a.ContractID AND am.Dataset = a.Dataset
AND am.Hole_id = a.Hole_id AND am.depth_FROM = a.depth_FROM
{0}
GROUP BY Item_CategORy
``` |
| Chart:
Total vs target Cost | TotalVsTargetCostSource | ```sql
SELECT
R.Log_Date
,R.Total_Cost
,R.Daily_Total_Budget_Costs
,[14D_Avg_Daily_Cost] = AVG(Total_Cost) OVER(ORDER BY dateORder ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)
,Daily_Cost_Cum = SUM(Total_Cost) OVER(ORDER BY dateORder)
,Daily_Cost_Cum_Target = SUM(Daily_Total_Budget_Costs) OVER(ORDER BY dateORder)
,dateorder
FROM
(SELECT
Log_Date,
CAST(SUM(Total_Cost) AS NUMERIC(36, 2)) Total_Cost,
CAST(SUM(Daily_Total_Budget_Costs) AS NUMERIC(36, 2)) Daily_Total_Budget_Costs,
dateORder
FROM(
SELECT
RPM.Log_Date, RPM.dateORder,Combined_Total_Revenue AS Total_Cost,Daily_Total_Budget_Costs
FROM(
SELECT
CONVERT( CHAR(30), a.Log_Date, 103) AS Log_Date,
CONVERT( DATE, a.Log_Date, 103) dateORder,
SUM(TotalCost) AS Combined_Total_Revenue,
SUM(b.Budget_Quantity)/(DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,a.Log_Date),0)))) AS Daily_Total_Budget_Costs
FROM(
SELECT
a.Dataset, a.Hole_ID, a.ContractID, MachineryID
, Log_Date,SUM(TotalCost) TotalCost
FROM tblVWDASHContractCosts a
LEFT OUTER JOIN dbo.tblContractProjectArea pa ON pa.ContractID = a.ContractID
LEFT OUTER JOIN dbo.tblContractActivityLogMetres am ON am.ActivityID = a.ActivityID
{0}
GROUP BY a.Dataset, a.Hole_ID, a.ContractID, MachineryID, Log_Date, Shift
) a
LEFT JOIN tblbudgets b ON
a.ContractID = b.RepORting_Type AND
b.RepORting_Period = ''Monthly'' AND
(Month(cast(b.Budget_Date AS Date)) = month(CAST(a.Log_Date AS DATE)) AND
Year(cast(b.Budget_Date AS Date)) = Year(CAST(a.Log_Date AS DATE)))
GROUP BY a.Log_Date
) RPM
) a1
GROUP BY Log_Date,dateorder
) R
ORDER BY dateorder;'
``` |
| ExpORt:
Total Activities ExpORt AND Table data | TotalActivitiesSource | ```sql
SELECT
a.ContractID, a.DataSet, a.Hole_ID, Project_Area, Shift, Log_Date,
Item_CategORy, Item_Code, MachineryID, Unit, Cost_Per_Unit,
CASE WHEN Is_Billable = 1 THEN ''Yes'' ELSE ''No'' END Is_Billable,
SUM(TotalCost) Total_Cost, SUM(Quantity) Quantity
FROM tblVWDASHContractCosts a
LEFT OUTER JOIN dbo.tblContractProjectArea b ON a.ContractID = b.ContractID
LEFT OUTER JOIN dbo.tblContractActivityLogMetres am ON am.ContractID = a.ContractID AND am.ActivityID = a.ActivityID AND am.Dataset = a.Dataset
AND am.Hole_id = a.Hole_id AND am.depth_FROM = a.depth_FROM
{0}
GROUP BY
a.ContractID, a.DataSet, a.Hole_ID,Project_Area, MachineryID, Shift,
Log_Date,Item_Category,Item_Code, Unit, Cost_Per_Unit, Is_Billable
``` |
| Filters | DrillingSummaryFilterSource | ```sql
SELECT DISTINCT a.DataSet, a.Project_Area, b.Activity_Log_Date, c.Hole_ID, c.Drill_Item
FROM dbo.tblContractProjectArea a
INNER JOIN dbo.tblContractActivityLog b
ON a.ContractID = b.ContractID AND a.Project_Area = b.Project_Area AND a.Site = b.Site
INNER JOIN dbo.tblContractActivityLogMetres c
ON b.ContractID = c.ContractID AND b.ActivityID = c.ActivityID
INNER JOIN (
SELECT REPLACE(DataSet, ''"'', '''') AS DataSet
FROM dbo.tblSYSUserDataset
WHERE UserName = @UserName) d
ON a.DataSet = d.DataSet
WHERE b.Activity_Log_Date @Activity_Log_Date
``` |
| Filters:DatePeriod | filtDatePeriod | ```sql
SELECT DatePeriod, DateFROM + ':' + DateTo AS DateDesc FROM (
SELECT convert(varchar,convert(date,DATEADD(dd, datediff(dd, 0, DATEADD(dd, - 1, GetDate())), 0)),101) AS DateFROM
,convert(varchar,convert(date,DATEADD(dd, datediff(dd, 0, DATEADD(dd, - 1, GetDate())), 0)),101) AS DateTo,
'Yesterday' AS DatePeriod, 1 AS [order]
UNION
SELECT convert(varchar,convert(date,DATEADD(dd, datediff(dd, 0, DATEADD(dd, - (datepart(dw, GetDate()) + 6), GetDate())), 0)),101) AS DateFROM
,convert(varchar,convert(date,DATEADD(dd, datediff(dd, 0, DATEADD(dd, - (datepart(dw, GetDate())), GetDate())), 0)),101) AS DateTo,
'Last Week' AS DatePeriod, 2 AS [order]
UNION
SELECT convert(varchar,convert(date,DATEADD(m, datediff(m, 0, DATEADD(m, - 1, GetDate())), 0)),101) AS DateFROM
,convert(varchar,convert(date,DATEADD(d, - 1, DATEADD(m, datediff(m, 0, GetDate()), 0))),101) AS DateTo,
'Last Month' AS DatePeriod, 4 AS [order]
UNION
SELECT convert(varchar,convert(date,DATEADD(QUARTER, DATEDIFF(QUARTER, 0, DATEADD(QUARTER, - 1, GETDATE())), 0)),101) AS DateFROM
,convert(varchar,convert(date,DATEADD(d, - 1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))),101) AS DateTo,
'Last Quarter' AS DatePeriod, 7 AS [order]
UNION
SELECT convert(varchar,convert(date,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)),101) AS DateFROM
,convert(varchar,convert(date,DATEADD(d, Datediff(d, 0, GETDATE()), 0)),101) AS DateTo,
'Month to Date' AS DatePeriod, 5 AS [order]
UNION
SELECT convert(varchar,convert(date,DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)),101) AS DateFROM
,convert(varchar,convert(date,DATEADD(d, Datediff(d, 0, GETDATE()), 0)),101) AS DateTo,
'Year to Date' AS DatePeriod, 8 AS [order]
UNION
SELECT convert(varchar,GETDATE(),101) AS DateFROM
,convert(varchar,GETDATE(),101) AS DateTo,
'Today' AS DatePeriod, 2 AS [order]
UNION
SELECT CONVERT(VARCHAR,DATEADD(dd,-(DAY(GetDate())-1),GetDate()),101) AS DateFROM
,CONVERT(VARCHAR,DATEADD(dd,-(DAY(DATEADD(mm,1,GetDate()))),
DATEADD(mm,1,GetDate())),101) AS DateTo,
'Current Month' AS DatePeriod, 6 AS [order]
UNION
SELECT
null AS DateFROM,
null AS DateTo,
'Custom' AS DatePeriod, 0 AS [order]
)a
``` |