T-SQL CASE (SQL Server 2000)

Question

I have a T-SQL query that I need to total out the counts for the CASE statements.

I tried to add a UNION but I am getting an error:

All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.

Any ideas? Thanks.

Query:

SELECT 
   CustomerID, Name, DueDate, 
   CASE WHEN DATEDIFF(Month, PaymentDate, DueDate) >= 1 
            THEN PaymentAmount ELSE 0 
   END AS [Early],
   CASE WHEN DATEDIFF(Month, PaymentDate, DueDate) >=0 
            THEN PaymentAmount ELSE 0 
   END AS [On Time],
   CASE WHEN DATEDIFF(Month, PaymentDate, DueDate) = -1 
            THEN PaymentAmount ELSE 0 
   END AS [Late]
FROM 
    Customers 
WHERE 
    DATEDIFF(MONTH, PaymentDate,DueDate), GetDate()) = 1
    AND PaymentAmount= DuesAmount

UNION

SELECT 
    '-Total', '', CustomerID, Name, DueDate,
    SUM(CASE WHEN DATEDIFF(Month, PaymentDate, DueDate) >= 1 
               THEN PaymentAmount ELSE 0 END) AS [Early],
    SUM(CASE WHEN DATEDIFF(Month, PaymentDate, DueDate) >= 0 
               THEN PaymentAmount ELSE 0 END) AS [On Time],
    SUM(CASE WHEN DATEDIFF(Month, PaymentDate, DueDate) = -1 
               THEN PaymentAmount ELSE 0 END) AS [Late]
FROM 
    Customers
WHERE 
    DATEDIFF(MONTH, PaymentDate,DueDate), GetDate()) = 1
    AND PaymentAmount = DuesAmount 

Answer 1

The error says, "All queries in a SQL statement containing a UNION operator must have an equal number of expressions in their target lists." What this means is that each SELECT must return the same number of expressions.

In the code sample you gave us, the first SELECT statement returns 6 expressions, while the second one returns 8:

SELECT CustomerID,                -- 1
       Name,                      -- 2
       DueDate,                   -- 3
       CASE ... END AS [Early],   -- 4
       CASE ... END AS [On Time], -- 5
       CASE ... END AS [Late]     -- 6
...
UNION
SELECT '-Total',                       -- 1
       '',                             -- 2
       CustomerID,                     -- 3
       Name,                           -- 4
       DueDate,                        -- 5
       SUM(CASE ... END) AS [Early],   -- 6
       SUM(CASE ... END) AS [On Time], -- 7
       SUM(CASE ... END) AS [Late]     -- 8
...

See how the first SELECT returns 6 expressions, but the second returns 8? In a UNION, all the SELECT statements must return the same number of expressions.

You can return NULL in the first query for columns that don't have a match in the second, if necessary. For example:

SELECT NULL as [RowType],         -- 1
       NULL as [Padding],         -- 2
       CustomerID,                -- 3
       Name,                      -- 4
       DueDate,                   -- 5
       CASE ... END AS [Early],   -- 6
       CASE ... END AS [On Time], -- 7
       CASE ... END AS [Late]     -- 8
...
UNION
SELECT '-Total',                       -- 1
       '',                             -- 2
       CustomerID,                     -- 3
       Name,                           -- 4
       DueDate,                        -- 5
       SUM(CASE ... END) AS [Early],   -- 6
       SUM(CASE ... END) AS [On Time], -- 7
       SUM(CASE ... END) AS [Late]     -- 8
...

Also, note that you don't have a comma after the DueDate column.