0

I am trying to combine these two queries into one. Can someone assist?

Query 1:
--------
SELECT 
  COUNT(Receiving_Log.Receiving_Log_Key) AS [Unresolved  Issues], 
  HUB.HUB_Name, 
  AVG(DATEDIFF(day, Receiving_Log.Rec_Date, GETDATE())) AS Avg
FROM HUB INNER JOIN Receiving_Log ON HUB.HUB_Key = Receiving_Log.HUB_Key
WHERE (Receiving_Log.Resolved = 0 )
GROUP BY HUB.HUB_Name

Query 2:
--------    
SELECT 
  COUNT(Receiving_Log.urgent) AS [Urgent  Issues], 
  HUB.HUB_Name, 
  AVG(DATEDIFF(day, Receiving_Log.Rec_Date, GETDATE())) AS Avg
FROM HUB INNER JOIN Receiving_Log ON HUB.HUB_Key = Receiving_Log.HUB_Key
WHERE (Receiving_Log.urgent = 0 and Receiving_Log.Resolved = 0)
GROUP BY HUB.HUB_Name
jpw
  • 44,361
  • 6
  • 66
  • 86
Jessy Robinson
  • 11
  • 3

1 Answers1

1

Since the only thing that makes the queries different is the condition used in the where clause you should be able to move the conditions into case expressions and do it like this:

SELECT        
    SUM(CASE WHEN Receiving_Log.urgent = 0 AND Receiving_Log.Resolved = 0 THEN 1 ELSE 0 END) AS [Urgent  Issues], 
    SUM(CASE WHEN Receiving_Log.Resolved = 0 THEN 1 ELSE 0 END) AS [Unresolved  Issues], 
    HUB.HUB_Name, 
    AVG(DATEDIFF(day, CASE WHEN Receiving_Log.Resolved = 0 THEN Receiving_Log.Rec_Date END, GETDATE())) AS Avg_UNRESOLVED, 
    AVG(DATEDIFF(day, CASE WHEN Receiving_Log.urgent = 0 AND Receiving_Log.Resolved = 0 THEN Receiving_Log.Rec_Date END, GETDATE())) AS Avg_URGENT
FROM HUB 
INNER JOIN Receiving_Log ON HUB.HUB_Key = Receiving_Log.HUB_Key
GROUP BY HUB.HUB_Name;

This would produce a result like:

Urgent Issues | Unresolved Issues | HUB_Name | Avg_UNRESOLVED | Avg_URGENT

Another option would be to merge the results from the two queries using union all.

jpw
  • 44,361
  • 6
  • 66
  • 86