I want to add RuntimePercentage column to my existing Uptime query for a report. Below is the current queries I use now. To do this I need to join another table results to make it happen but I haven’t been able to find a solution. The where clause gives me a error because UptimeMin is only in table a.
How RuntimePercentage is calculated as:
(IdletimeMin/60) IdletimeHours - Total current monthly hours /UptimeMin/60 UptimeHours = RuntimePercentage
Current uptime query
SELECT
a.StackNbr,
(SUM(a.UptimeMin)) / 60 UptimeHours,
ROUND((SUM(a.UptimeMin) / ((DATEDIFF(d, DATEADD(month, DATEDIFF(month, 0, getDate()), 0), DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)) * 24) * 60)) * 100, 2) UptimePercentage,
(((DATEDIFF(d, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)) * 24) * 60) - SUM(a.UptimeMin)) / 60 DowntimeHours,
ROUND((((DATEDIFF(d, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)) * 24) * 60) - SUM(a.UptimeMin)) / ((DATEDIFF(d, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)) * 24) * 60) * 100, 2) DowntimePercentage,
COUNT(UptimeMin) Count
FROM
IngStackerUptime a
WHERE
a.UptimeMin > 0
AND a.DateTm BETWEEN DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
AND DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)
GROUP BY
a.StackNbr
ORDER BY
a.StackNbr
Current query results are:
StackNbr / UptimeHours / UptimePercentage / DowntimeHours / DowntimePercentage / Count
1 / 85.335 / 12.26 / 610.665 / 87.74 / 1077
2 / 13.457 / 1.93 / 682.543 / 98.07 / 185
3 / 9.998 / 1.44 / 686.002 / 98.56 / 137
4 / 89.121 / 12.8 / 606.879 / 87.2 / 1096
Current idletime query
SELECT
StackNbr,
(SUM(IdletimeMin)) / 60 IdletimeHours,
ROUND((SUM(IdletimeMin) / ((DATEDIFF(d, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)) * 24) * 60)) * 100, 2) IdletimePercentage,
COUNT(IdletimeMin) Count
FROM
IngStackerIdletime
WHERE
IdletimeMin > 0
AND DateTm BETWEEN DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
AND DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)
GROUP BY
StackNbr
ORDER BY
StackNbr
Current query results
StackNbr / IdletimeHours/ IdletimePercentage /Count
1 / 112.531 / 16.17 / 1363
2 / 190.464 / 27.37 / 2278
3 / 195.588 / 28.1 / 2336
4 / 116.015 / 16.67 / 1403
Advertisement
Answer
As best I can tell from your question you just want to carry out a straight join between your 2 datasets, which you do by using them as sub-queries as shown below. I wasn’t 100% sure on how you wanted to calculate “Total current monthly hours” so added a couple of options.
Personally I would highly recommend splitting out your date calculations so you only carry them out once, rather than repeating that complex logic again and again which not only makes the query hard to read, but means the potential for a mistake is massive.
SELECT X.*, Y.*
-- Calculation using total hours in the month
, (Y.IdletimeHours - ((DATEDIFF(day,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1))+1)*24)/X.UptimeHours) RuntimePercentage
-- Calculation using hours to date in the month
, (Y.IdletimeHours - ((DATEDIFF(day,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), CONVERT(date, GETDATE()))+1)*24)/X.UptimeHours) RuntimePercentage
FROM (
SELECT a.StackNbr
, (SUM(a.UptimeMin))/60 UptimeHours
, ROUND((SUM(a.UptimeMin) / ((DATEDIFF(d,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)) * 24) * 60))*100,2) UptimePercentage
, (((DATEDIFF(d,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)) * 24) * 60) - SUM(a.UptimeMin))/60 DowntimeHours
, ROUND((((DATEDIFF(d,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)) * 24) * 60) - SUM(a.UptimeMin)) / ((DATEDIFF(d,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)) * 24) * 60) * 100,2) DowntimePercentage
, COUNT(UptimeMin) [Count]
FROM IngStackerUptime a
WHERE a.UptimeMin > 0 AND a.DateTm BETWEEN DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AND DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)
GROUP BY a.StackNbr
) X
INNER JOIN (
SELECT StackNbr
, (SUM(IdletimeMin)) / 60 IdletimeHours
, ROUND((SUM(IdletimeMin) / ((DATEDIFF(d, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)) * 24) * 60)) * 100, 2) IdletimePercentage
, COUNT(IdletimeMin) Count
FROM IngStackerIdletime
WHERE IdletimeMin > 0 AND DateTm BETWEEN DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AND DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)
GROUP BY StackNbr
) Y on Y.StackNbr = X.StackNbr
ORDER BY X.StackNbr;
Here is some skeleton code for how you might extract out the date calculations to avoid repeating them:
SELECT X.*, Y.*
FROM (
VALUES (DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1))
) D (StartDate, EndDate)
CROSS APPLY (
SELECT a.StackNbr
FROM IngStackerUptime a
WHERE a.UptimeMin > 0 AND a.DateTm BETWEEN D.StartDate AND D.EndDate
GROUP BY a.StackNbr
) X
CROSS APPLY (
SELECT StackNbr
FROM IngStackerIdletime
WHERE IdletimeMin > 0 AND DateTm BETWEEN D.StartDate AND D.EndDate
GROUP BY StackNbr
) Y
WHERE Y.StackNbr = X.StackNbr
ORDER BY X.StackNbr;
Note: Consistent layout, casing and formatting make a query much easier to read and manage.
Also, I’m not a fan of BETWEEN because it unintuitive what the boundaries are and it doesn’t work if you accidentally end up with a time component in your datetime ranges. Date >= Startdate and Date < dateadd(day, 1, EndDate) is more obvious and reliable (or Date >= Startdate and Date < dateadd(month, 1, StartDate)).