Pivoting with NULL Dates

Question

I'm attempting the pivot below:

SELECT
   EmpID as ID,    
   [1] as January,    
   [2] as February,     
   [3] as March,     
   [4] as April,     
   [5] as May,     
   [6] as June,     
   [7] as July,     
   [8] as August,
   [9] as September,     
   [10] as October,     
   [11] as November,     
   [12] as December,     
   isnull((processeddate),' ') as 'Null'
FROM
(
  SELECT EmpID, EmpDeductAmt, month(ProcessedDate) as TMonth
  FROM [dbo].[Invoices] where Inv_Number not in ('1035','1039')
)
AS source 
PIVOT
(
  sum(EmpDeductAmt) for TMonth in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) 
AS pvtmonth
ORDER BY EmpID;

However, I receive an error message of "Invalid column name 'processeddate' upon hitting F5.

I'm fairly green, but the code worked beautifully when the isnull((processed date),' ') as 'Null' code was left off.

The desired output would consist of columns pertaining to each month in addition to a column for NULL months, as some of the records in my dataset have NULL as their date. Failure to account for the NULL dates shortchanges the dollar amount arrived out. The rows are arranged by employee identification number.

Is there a suggested remedy for this issue?

Many, many thanks!

Answer 1

So at this point in the code:

isnull((processeddate),' ') as 'Null'

The column processeddate is not visible to SQL Server, because you are selecting available fields from the result of the following query:

SELECT EmpID, EmpDeductAmt, month(ProcessedDate) as TMonth
  FROM [dbo].[Invoices] where Inv_Number not in ('1035','1039')

...along with the pivoted columns [1] through [12].

You either need to include ProcessedDate in the above query, or exclude it from the outer SELECT.