Joining another tables results to add to a existing query-CodePudding

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

CodePudding user response：

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)).

CodePudding user response：

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,
(Sum(b.IdletimeMin))/60 b.IdletimeHours, round((Sum(b.IdletimeMin) / ((DATEDIFF(d,DATEADD(b.month, DATEDIFF(b.month, 0, getDate()), 0),DATEADD(b.month, DATEDIFF(b.month, -1, getDate()), -1)) * 24) * 60))*100,2) b.IdletimePercentage, 
Count (b.IdletimeMin) Count
Count (UptimeMin) Count
From IngStackerUptime a
,IngStackerIdletime b
Where a.UptimeMin > 0 and a.DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
and a.stacknbr = b.stacknbr
and b.IdletimeMin > 0 and b.DateTm Between DATEADD(b.month, DATEDIFF(b.month, 0, getDate()), 0) and DATEADD(b.month, DATEDIFF(b.month, -1, getDate()), -1)
Group by a.StackNbr Order by a.StackNbr