SQL多表连接构建理想表报错求助：UNION操作列数不匹配

Hey there, let's resolve that Msg 205 error first—it's happening because the three queries in your Work_Matrix CTE don't have the same number of columns, which is a hard requirement for UNION ALL. Let's fix that, then adjust the query to fully meet your requirements for the IdealTable.

Step 1: Break Down the Error #

Msg 205, Level 16, State 1, Line 4
使用UNION、INTERSECT或EXCEPT运算符组合的所有查询必须在目标列表中具有相同数量的表达式。

Your first SELECT in Work_Matrix includes 6 columns (EmployeeID, [Date], ShiftType, ShiftDuration, Duration_Off, OT_Duration), but the subsequent Leave and Overtime queries only have 5 each. We need to add placeholder columns (using NULL where appropriate) to make all three queries match in column count and data types.

Step 2: Revised Query with Full Fixes #

Here's the corrected SQL that fixes the column mismatch, handles NULL values properly for calculations, and aligns with all your requirements:

USE [SMRT Dashboard]
GO
;With Dates AS (
 -- Get all unique dates across Roster, Leave, and Overtime tables
 SELECT [Date] FROM dbo.Roster
 UNION
 SELECT [Date] FROM dbo.Leave
 UNION
 SELECT [Date] FROM dbo.Overtime
), Work_Matrix AS (
 -- Roster entries: include all columns, apply default ShiftDuration if NULL
 SELECT 
  EmployeeID,
  [Date],
  ShiftType,
  ISNULL(ShiftDuration, 8.25) AS ShiftDuration, -- Enforce default value
  CAST(NULL AS Decimal(30,2)) AS Duration_Off,
  CAST(NULL AS Decimal(30,2)) AS OT_Duration
 FROM dbo.Roster
 UNION ALL
 -- Leave entries: fill missing columns with NULL placeholders
 SELECT 
  EmployeeID,
  [Date],
  NULL AS ShiftType,
  NULL AS ShiftDuration,
  Duration_Off,
  NULL AS OT_Duration
 FROM dbo.Leave
 UNION ALL
 -- Overtime entries: fill missing columns with NULL placeholders
 SELECT 
  EmployeeID,
  [Date],
  NULL AS ShiftType,
  NULL AS ShiftDuration,
  NULL AS Duration_Off,
  OT_Duration
 FROM dbo.Overtime
)
SELECT 
 d.[Date],
 e.EmployeeID,
 e.GivenName,
 e.FamilyName,
 e.TeamID,
 MAX(w.ShiftType) AS ShiftType, -- Grab the single shift type per employee-date
 ISNULL(SUM(w.ShiftDuration), 8.25) AS ShiftDuration, -- Fallback to default if no roster entry
 ISNULL(SUM(w.Duration_Off), 0) AS Duration_Off, -- Treat no leave as 0
 ISNULL(SUM(w.OT_Duration), 0) AS OT_Duration, -- Treat no overtime as 0
 -- Calculate Total_Hours safely, handling all NULL scenarios
 ISNULL(SUM(w.ShiftDuration), 8.25) + ISNULL(SUM(w.OT_Duration), 0) - ISNULL(SUM(w.Duration_Off), 0) AS Total_Hours
FROM Dates d
INNER JOIN Work_Matrix w ON d.[Date] = w.[Date]
JOIN dbo.Employee e ON e.EmployeeID = w.EmployeeID
GROUP BY d.[Date], e.EmployeeID, e.GivenName, e.FamilyName, e.TeamID

Step 3: Key Improvements Explained #

• Fixed Column Mismatch: Each query in Work_Matrix now has 6 columns, satisfying the UNION ALL requirement.
• Default ShiftDuration: Used ISNULL(ShiftDuration, 8.25) to enforce your default value whenever the Roster's ShiftDuration is missing, both in the CTE and final calculation.
• Safe NULL Handling: Converted NULL values for Duration_Off and OT_Duration to 0 using ISNULL()—this prevents NULLs from breaking the Total_Hours formula (since NULL + number = NULL otherwise).
• Cleaner Aggregation: Moved grouping to the main query for readability, and used MAX(ShiftType) since each employee-date pair will only have one valid ShiftType (from the Roster table).

Step 4: Edge Case Coverage #

This query handles all your specified scenarios:

• Employees with a Roster entry but no Leave/Overtime: Duration_Off and OT_Duration show 0.
• Employees with Leave/Overtime but no Roster entry: ShiftDuration falls back to 8.25.
• All unique dates across all three tables are included, so no dates are missing from your IdealTable.

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