width用法 使查询语句更清晰

WITH Temp AS                                                        ---With 1  Temp
 (SELECT OOD_PROVINCE as Region,
         OnOff.VI_ID,
         SUM(OOD_WORKHOURS) as TotalWorkHours
    FROM ON_OFF_DAY201403 OnOff
   WHERE OOD_Day >= date '2014-3-28'
     AND OOD_Day <= date '2014-4-4'
     AND OOD_PROVINCE IS NOT NULL
     AND VI_OWN_TYPE = 3
   GROUP BY OOD_PROVINCE, OnOff.VI_ID
  UNION ALL
  SELECT OOD_PROVINCE as Region,
         OnOff.VI_ID,
         SUM(OOD_WORKHOURS) as TotalWorkHours
    FROM ON_OFF_DAY201404 OnOff
   WHERE OOD_Day >= date '2014-3-28'
     AND OOD_Day <= date '2014-4-4'
     AND OOD_PROVINCE IS NOT NULL
     AND VI_OWN_TYPE = 3
   GROUP BY OOD_PROVINCE, OnOff.VI_ID),
  
  
VehicleStats AS                                                         -- With 2 VehicleStats
 (SELECT Region, Vehicle.VI_ID, SUM(TotalWorkHours) as TotalWorkHours
    FROM Temp                                                          ---With 1  Temp
    JOIN Vehicle_Info Vehicle
      ON Temp.VI_ID = Vehicle.VI_ID
   WHERE 1 = 1
   GROUP BY Region, Vehicle.VI_ID)
  
  
SELECT Region,
       COUNT(*) AS VehicleCount,
       SUM(CASE
             WHEN TotalWorkHours > 0 THEN
              1
             ELSE
              0
           END) AS HasWorkHourCount,
       SUM(CASE
             WHEN TotalWorkHours > 2 THEN
              1
             ELSE
              0
           END) AS AboveWorkHourCount
  FROM VehicleStats                                                 -- With 2 VehicleStats
 GROUP BY Region
 ORDER BY AboveWorkHourCount DESC