将三个分组SELECT查询合并为单个查询

Got it, let's merge those three separate PM work order count queries into one, while preserving all your original statistical logic. Here's the consolidated solution using conditional aggregation and a left join to handle the forecast data:

SELECT 
    W.LOCATION,
    -- Count of on-time PM work orders
    COUNT(CASE WHEN W.actfinish <= W.targcompdate THEN W.WONUM END) AS OnTimePMWOs,
    -- Count of past-due PM work orders (matches your original condition)
    COUNT(CASE WHEN W.actfinish >= W.targcompdate THEN W.WONUM END) AS PastDuePMWOs,
    -- Count of PM work orders with a forecast in the next 30 days
    COUNT(CASE WHEN P.forecastdate >= GETDATE() + 30 THEN W.WONUM END) AS Forecast30days
FROM WORKORDER W
LEFT JOIN PMFORECAST P 
    ON W.CHANGEDATE = P.CHANGEDATE
WHERE W.worktype = 'pm'
GROUP BY W.LOCATION

Key Notes: #

• Conditional Aggregation: We use COUNT(CASE ... END) to calculate each metric within the same GROUP BY operation, which is more efficient than running three separate queries against the WORKORDER table.
• Left Join: Using LEFT JOIN ensures we don't exclude locations that have PM work orders but no corresponding entries in the PMFORECAST table (those will show 0 for Forecast30days).
• Preserved Logic: All your original filtering conditions are retained:
  • On-time checks: actfinish <= targcompdate
  • Past-due checks: actfinish >= targcompdate (if this was a typo and you meant actfinish > targcompdate, just adjust the condition here)
  • Forecast window: forecastdate >= GETDATE() + 30 linked via CHANGEDATE

If you want to include locations that might have no PM work orders at all (showing 0 for all metrics), you'd need to adjust to start from a locations table and left join to WORKORDER, but based on your original queries, assuming you only care about locations with existing PM work orders, the above should work perfectly.

内容的提问来源于stack exchange，提问作者Awesome Dude